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
Powereports
Helper I
Helper I

Rolling 12 months Retention

Hi Users,

 I have a measure which gives me the retention  as below. I looking for calculating the rolling retention for 12 months from a date. My question can i use the below retention measure to calculate Rolling retention for a date ? If yes then how do i start with thedax for it?

 

Retention  = DIVIDE([Staff Retained],[SOP Active staff],0) *100
 
Staff Retained = [SOP Active staff] - [Terminations]
 
SOP Active staff =
VAR _FirstDayCurrentMonth =
MIN( CalFiscal[Date] )
RETURN
CALCULATE (
COUNT ('Hires term 2 4'[EMPLID]),
FILTER (
'Hires term 2 4',
'Hires term 2 4'[LAST_HIRE_DT]<= _FirstDayCurrentMonth
&& (
'Hires term 2 4'[TERMINATION_DT] >= _FirstDayCurrentMonth
|| ISBLANK ( 'Hires term 2 4'[TERMINATION_DT]))))
 
 
Terminations =
CALCULATE(COUNT(Turnover[TERMINATION_DT]),
USERELATIONSHIP(Turnover[TERMINATION_DT],CalFiscal[Date]))
2 ACCEPTED SOLUTIONS
FarhanAhmed
Community Champion
Community Champion

you can give this a try and see the results.

You just need to make sure that you have a date table that is marked as Date Table in your model. In the below example, the "DimDate" is the Date/Calendar table.

Rolling 12 Month Retention = CALCULATE(Retention,DATESINPERIOD(DimDate[DateSecAlternateKey],MAX(DimDate[DateSecAlternateKey]),-12,MONTH))

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




View solution in original post

It worked Farhan.. Thanks for the solution.

View solution in original post

2 REPLIES 2
FarhanAhmed
Community Champion
Community Champion

you can give this a try and see the results.

You just need to make sure that you have a date table that is marked as Date Table in your model. In the below example, the "DimDate" is the Date/Calendar table.

Rolling 12 Month Retention = CALCULATE(Retention,DATESINPERIOD(DimDate[DateSecAlternateKey],MAX(DimDate[DateSecAlternateKey]),-12,MONTH))

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




It worked Farhan.. Thanks for the solution.

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