Forum Discussion
moving monthly average
Okay, I know there are many that have stumbled across this but I don't see any answers here that I can use. My fiscal year starts in April and I need to calculate a 6-month moving average of sales qty. The problem is, is that my date table starts in APR 2015, and even though I filter out FY15, the result set appears to include it in the values when I only want to start from FY16 onward. Any help on this will be appreciated!!
My calculation is as follows:
Qty 12M Months (act) = CALCULATE (
CALCULATE ( COUNTROWS ( VALUES ( Dates[FYM] ) ), Sales ),
DATESBETWEEN (
Dates[Date],
SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( Dates[Date] ) ) ),
LASTDATE ( Dates[Date] )
)
)
3 Replies
Assuming you have a datekey in your datetable., you should still be able to slice by finanical year.
I usually create my rolling averages like this:
1. Create a total sales measure: SalesQty = CALCULATE(SUM(SalesTable[Sales]]))
2. Qty12M Months (act) = CALCULATE([SalesQty],DATESINPERIOD(DateTable[DateKey],LASTDATE(DateTable[DateKey]),-12,MONTH))
Or for a 6 month moving average
Qty6M Months (act) = CALCULATE([ActualHeadcountSUM],DATESINPERIOD(DateTable[DateKey],LASTDATE(DateTable[DateKey]),-6,MONTH))
Hope this helps.
Well, it is kind of working, but even if I start the month at the first month of the fiscal year, it is still taking all dates available to the data model into account. Please let me know if there is something I may be doing wrong?
Hi sdmikejr,
Can you provide some detail info about this? (e.g. your table' structure, sample data)
Regards,
Xiaoxin Sheng
