-
1. Re: Monthly avg sales issue
Thomas McCullough Dec 21, 2015 6:23 AM (in response to Jessy Cool)Hi Jesse,
The attached workbook shows a solution to get average monthly sales by Product Category using the WINDOW_AVG table calculation function.Cheers,
Tom
-
Avg monthly sales.twbx 410.4 KB
-
-
2. Re: Monthly avg sales issue
Rody Zakovich Dec 21, 2015 7:37 AM (in response to Thomas McCullough)You could also use an LoD.
First step is to create a field that defines the Month and Year. Here are some options.
1. Create Custom Date Field
2. Create a calculated field that basically does the same thing
(DATEPART('year', [Order Date])*100 + DATEPART('month', [Order Date]))
3. Use Truncation
DATETRUNC('month', [Order Date])
Either of these will work, and they all give you a field to use in an LoD, i.e. Year and Month (2015-12)
Once you have that, you can just reference it using an INCLUDE LoD
AVG({ INCLUDE [Order Date (Month / Year)] : SUM([Sales]) })
Since you are using an LoD, you don't need to worry about adding a LAST() function, or adding the Order Date to the Detail shelf. And you can filter, without issues.
Regards,
Rody