Rolling 12 Month calculation

[Anonymous]

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 and I have created a calendar in my data using calendarAuto() function.

Running Headcount =

CALCULATE (

SUMX ( Employee_Details, Employee_Details[HeadCount] ),

FILTER (

ALL ( Employee_Details[DateRef] ),

AND (

Employee_Details[DateRef] <= MAX ( 'Calendar'[Date] ),

DATEADD ( 'Calendar'[Date], 12, MONTH ) > MAX ( 'Calendar'[Date] ))))

This formula is calculating right numbers till dec'2019 but for 2020 it's giving an incorrect number for eg Jan'20 cumulative headcount should be from Feb'19 to Jan'20 but the formula is calculating only for Feb'19 till Dec'19 and so on

Below is the actual calculation is done by measure and sample on how my data looks

Measure calculation

DateRef	HeadCount	Rolling 12 Month (DAX)	Rolling 12 Month (Excel)	Check
30-01-18 0:00	7610	88885	88885	TRUE
28-02-18 0:00	7715	89070	89070	TRUE
30-03-18 0:00	7945	89638	89638	TRUE
30-04-18 0:00	8203	90552	90552	TRUE
30-05-18 0:00	8450	91796	91796	TRUE
30-06-18 0:00	8809	93360	93360	TRUE
30-07-18 0:00	9322	95359	95359	TRUE
30-08-18 0:00	9678	97608	97608	TRUE
30-09-18 0:00	9979	100146	100146	TRUE
30-10-18 0:00	10077	102764	102764	TRUE
30-11-18 0:00	10063	105367	105367	TRUE
30-12-18 0:00	10342	108193	108193	TRUE
30-01-19 0:00	10394	110977	110977	TRUE
28-02-19 0:00	10486	113748	113748	TRUE
30-03-19 0:00	10623	116426	116426	TRUE
30-04-19 0:00	10762	118985	118985	TRUE
30-05-19 0:00	10932	121467	121467	TRUE
30-06-19 0:00	11292	123950	123950	TRUE
30-07-19 0:00	11765	126393	126393	TRUE
30-08-19 0:00	12344	129059	129059	TRUE
30-09-19 0:00	12841	131921	131921	TRUE
30-10-19 0:00	12826	134670	134670	TRUE
30-11-19 0:00	12836	137443	137443	TRUE
30-12-19 0:00	12881	139982	139982	TRUE
30-01-20 0:00	12857	129588	142445	FALSE
28-02-20 0:00	12816	119102	144775	FALSE
30-03-20 0:00	12791	108479	146943	FALSE
30-04-20 0:00	12831	97717	149012	FALSE

Sample Data

Employee ID	Month Tag	Year Tag	Date Ref	HC-Ex Tag	Heacount
1	Jan	2019	31-01-19	HC	1
2	Jan	2019	31-01-19	HC	1
3	Jan	2019	31-01-19	HC	1
4	Jan	2019	31-01-19	HC	1
5	Jan	2019	31-01-19	Ex	0
1	Feb	2019	28-02-19	HC	1
2	Feb	2019	28-02-19	HC	1
3	Feb	2019	28-02-19	HC	1
4	Feb	2019	28-02-19	Ex	0
1	Mar	2019	31-03-19	HC	1
2	Mar	2019	31-03-19	HC	1
3	Mar	2019	31-03-19	HC	1

[Anonymous]

// 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

[emilyzhu49]

This is perfect! Thanks!

[Mariusz · Accepted solution]

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]

@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