Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rolling 12 Month calculation

Hi    I am trying to calculate rolling 12-month headcount using below DAX formula, I have a data in monthly time series format with date ref column for month-end date of each month for each record ...
DATEADD
DAX
DAX Calculation
Rolling 12 Months
running total
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    Try this.

    Measure = 
CALCULATE(
    SUM( 'Table'[HeadCount] ),
    DATESINPERIOD( 'Table'[DateRef], MAX( 'Table'[DateRef] ), -12,  MONTH )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn


     

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous 

 

Try this.

Measure = 
CALCULATE(
    SUM( 'Table'[HeadCount] ),
    DATESINPERIOD( 'Table'[DateRef], MAX( 'Table'[DateRef] ), -12,  MONTH )
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Mariusz, this measure is not correct and will be returning wrong results before you know it. When calculating 12M moving avg, you must ensure that there are enough days in the calendar to cover 12M. If not, then the average is not covering 12M and should NOT be calculated.

So, in a word, to do this correctly you HAVE to have a proper Calendar in the model. See the measure I've written for details.

Best
D