cancel
Showing results for
Did you mean:
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.

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

1 ACCEPTED SOLUTION
Community Champion

Hi @Anonymous

Try this.

``````Measure =
CALCULATE(
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.

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.

var __lastVisibleDate = LASTDATE( 'Calendar'[Date] )
var __canMove12MBack =
NOT ISBLANK( dateadd( __lastVisibleDate, -1, Year) )
var __result =
CALCULATE(
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/

Best

D

Frequent Visitor

This is perfect! Thanks!

Community Champion

Hi @Anonymous

Try this.

``````Measure =
CALCULATE(
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.

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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors