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


     

Anonymous's avatar
Anonymous
Not applicable
6 years ago
// A proper Calendar table marked as the date table
// must be present for this to work correctly.
// It must be connected to the FactTable and 
// slicing must be done only through dimensions.
// In a word, all the best practices must be
// followed in the model.

HeadCount L12M = 
var __lastVisibleDate = LASTDATE( 'Calendar'[Date] )
var __canMove12MBack =
	NOT ISBLANK( dateadd( __lastVisibleDate, -1, Year) )
var __result =
	CALCULATE(
	    SUM( FactTable[HeadCount] ),
	    DATESINPERIOD(
	    	Calendar[Date],
	    	__lastVisibleDate,
	    	-1,
	    	Year
	    )
	)	
return
if( __canMove12MBack, __result )

 

Here's something about proper designs and time-intel:

 

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

 

https://www.youtube.com/watch?v=_quTwyvDfG0

 

Best

D