Forum Discussion

Spencer's avatar
Spencer
Helper II
10 years ago

Calculating Rolling Measure

Hi,

 

I have a table which displays how many people are in each department at the end of every month. 

I'm looking at creating a rolling 12 month measure that gives you the average amount of people across all departments for the past 12 months. (i.e. a headcount).

 

I've tried using the below formula but it is giving me values of around 9 and 10 for the most recent 12 months as a example, when that number should be around 300:

Rolling12mAvHeadcount = CALCULATE(AVERAGE(DeptHC[Headcount]),DATESINPERIOD(DeptHC[MonthPT],LASTDATE(DeptHC[MonthPT]),-12,MONTH))

 

I'm sure I've almost cracked it, just some guidance would be fantastic.

Thanks

Dataset - Headcount by Department per Month

Need Help

5 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago
    Moving AverageX 12 MONTHS :=
    AVERAGEX (
        DATESINPERIOD (
            DeptHC[MonthPT],
             LASTDATE(DeptHC[MonthPT]),
               -12,
                MONTH
        ),
        [DEPTHC HEADCOUNT]
    )
     
    This would solve your problem. 
    • Spencer's avatar
      Spencer
      Helper II
      9 years ago

      Hi BhaveshPatel thanks very much for your response.

      On the second last line of your formula, do you need a SUM or AVERAGE function before you name [DEPTHC HEADCOUNT]?

       

      Thanks