Forum Discussion

sdmikejr's avatar
sdmikejr
Helper I
9 years ago

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 qt...
Spencer's avatar
Spencer
Helper II
9 years ago

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.

 

  • sdmikejr's avatar
    sdmikejr
    Helper I
    9 years ago

    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?

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi sdmikejr,

       

      Can you provide some detail info about this? (e.g. your table' structure,  sample data)

       

      Regards,

      Xiaoxin Sheng