Attrition Formula

[Dhavales]

Month	HC End of M-1	Total	Non Voluntary	Voluntary	Monthly Attrition %	Annual Attrition %
Jun 19	782	21	5	16	2.05%	0.00%
Jul 19	762	8	7	1	0.13%	0.00%
Aug 19	752	26	6	20	2.66%	0.00%
Sep 19	748	18	5	13	1.74%	0.00%
Oct 19	773	8	1	7	0.91%	0.00%
Nov 19	781	13	7	6	0.77%	0.00%
Dec 19	807	17	5	12	1.49%	17.60%
Jan 20	812	5	1	4	0.49%	16.32%
Feb 20	836	20	10	10	1.20%	16.28%
Mar 20	834	15	3	12	1.44%	16.36%
Apr 20	850	17	4	13	1.53%	16.40%
May 20	854	5	0	5	0.59%	14.98%
May 20	854	5	0	5	=E19/B19	=SUM(F8:F19)
			Monthly %	= (Total - Non Voluntary)/Head Count for the month
			Annual %	= Sum(past 12 months monthly%)

Hi Friends,

I am trying to calculate the above in Power BI.. above example is done in excel.

How do i get the Annual% rolling past 12 months in a measure?

The one in Green colour is the formula of excel..

Please help

Thanks

Sachin

[Anonymous · Accepted solution]

Hi @Dhavales ,

Based on my understanding,Annual %= Sum(past 12 months monthly%) should be a summarize of the pasting 12 months,I dont understand why from Jun 19 to Nov 19,the result returns 0?And why the value in Dec 19 is higher than the later values.

I made a sample .pbix file to calculate the total value if I didnt understand wrong,pls see below:

Create a measure as below:

Annual Attrition% = 
var _mindate=CALCULATE(MAX('Table'[Date]),DATEADD('Table'[Date],-12,MONTH))
Return
SUMX(FILTER(ALL('Table'),'Table'[Date]>=_mindate&&'Table'[Date]<=MAX('Table'[Date])),'Table'[Monthly %])

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

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

[Dhavales]

Dear Kelly..

Super cool.... it definitely helped 

Thanks for the quick solution..

Regards

Sachin Dhavale