Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

DateRefHeadCountRolling 12 Month (DAX)Rolling 12 Month (Excel)Check
30-01-18 0:0076108888588885TRUE
28-02-18 0:0077158907089070TRUE
30-03-18 0:0079458963889638TRUE
30-04-18 0:0082039055290552TRUE
30-05-18 0:0084509179691796TRUE
30-06-18 0:0088099336093360TRUE
30-07-18 0:0093229535995359TRUE
30-08-18 0:0096789760897608TRUE
30-09-18 0:009979100146100146TRUE
30-10-18 0:0010077102764102764TRUE
30-11-18 0:0010063105367105367TRUE
30-12-18 0:0010342108193108193TRUE
30-01-19 0:0010394110977110977TRUE
28-02-19 0:0010486113748113748TRUE
30-03-19 0:0010623116426116426TRUE
30-04-19 0:0010762118985118985TRUE
30-05-19 0:0010932121467121467TRUE
30-06-19 0:0011292123950123950TRUE
30-07-19 0:0011765126393126393TRUE
30-08-19 0:0012344129059129059TRUE
30-09-19 0:0012841131921131921TRUE
30-10-19 0:0012826134670134670TRUE
30-11-19 0:0012836137443137443TRUE
30-12-19 0:0012881139982139982TRUE
30-01-20 0:0012857129588142445FALSE
28-02-20 0:0012816119102144775FALSE
30-03-20 0:0012791108479146943FALSE
30-04-20 0:001283197717149012FALSE

 

 

Sample Data

Employee IDMonth TagYear TagDate RefHC-Ex TagHeacount
1Jan201931-01-19HC1
2Jan201931-01-19HC1
3Jan201931-01-19HC1
4Jan201931-01-19HC1
5Jan201931-01-19Ex0
1Feb201928-02-19HC1
2Feb201928-02-19HC1
3Feb201928-02-19HC1
4Feb201928-02-19Ex0
1Mar201931-03-19HC1
2Mar201931-03-19HC1
3Mar201931-03-19HC1

 

 

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

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


 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

This is perfect! Thanks!

Mariusz
Community Champion
Community Champion

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
Not applicable

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors