Forum Discussion
Attrition Formula
| 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
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,
KellyDid I answer your question? Mark my post as a solution!
2 Replies
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,
KellyDid I answer your question? Mark my post as a solution!Dear Kelly..
Super cool.... it definitely helped
Thanks for the quick solution..
Regards
Sachin Dhavale
