Hi,
My requestor would like to see an average of the previous 6 weeks, for each of our locations.
I have used this in a calculated field to find the previous 6 weeks: Works ok.
[Reporting Dt]< DATEADD('day',-ISOWEEKDAY(TODAY()-1),TODAY())
AND
[Reporting Dt]> DATEADD('day',-42-ISOWEEKDAY(TODAY()),TODAY())
Then this to find each of the individual 6 weeks Average %'s: Works ok.
1-SUM( if [Current Fy Flag]= 'Y' then [Forecast Volume] end )/
SUM( if [Current Fy Flag]= 'Y' then [Volume] end )
Results: All good so far.
week 31 -14.40%
32 -11.05%
33 -10.63%
34 -4.52%
35 -8.5%
36 -2.58%
But now I need to Average these 6 to get -8.61% (ie SUM(6 percentages / 6)
Does anyone have any ideas?
Many thanks
The simplest way would be to use WINDOW_AVG and filter to the 6 weeks you want to calculate over (and then hide what you didn't want to see using LAST()=0 as a filter). Depending on what else you want to do, you could also use something like:
AVG({INCLUDE DATEPART('week', [Order Date]): [Profit Ratio]})Or, with the LoD, the weeks aren't required:
