Rolling 12 months Retention

[Powereports]

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

[FarhanAhmed · Accepted solution]

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

[FarhanAhmed · Accepted solution]

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

[Powereports · Accepted solution]

It worked Farhan.. Thanks for the solution.