Need help with DAX formula to calculate attrition %

[Anonymous]

Hi All,

I need help in calculating overall YTD attrition %. Below is the data

Site	Month	Total Attrition	HC	YTD Attrition %
Site 1	1-Sep-19	13	612	25%
Site 2	1-Sep-19	7	412	20%
Site 1	1-Oct-19	15	716	25%
Site 2	1-Oct-19	11	504	24%
Site 1	1-Nov-19	19	709	28%
Site 2	1-Nov-19	14	508	27%
Site 1	1-Dec-19	18	684	29%
Site 2	1-Dec-19	9	518	25%
Site 1	1-Jan-20	15	609	29%
Site 2	1-Jan-20	12	486.5	26%
Site 1	1-Feb-20	23	519	32%
Site 2	1-Feb-20	8	437	26%
Site 1	1-Mar-20	9	452	31%
Site 2	1-Mar-20	6	436	24%
Site 1	1-Apr-20	3	434	29%
Site 2	1-Apr-20	1	464.5	22%
Site 1	1-May-20	2	436	27%
Site 2	1-May-20	2	492	20%
Site 1	1-Jun-20	5	411	26%
Site 2	1-Jun-20	3	496	18%
Site 1	1-Jul-20	6	388	26%
Site 2	1-Jul-20	2	484	17%
Site 1	1-Aug-20	4	388	25%
Site 2	1-Aug-20	2	483	15%
Site 1	1-Sep-20	3	355	10%
Site 2	1-Sep-20	1	347.5	3%
Site 1	1-Oct-20	4	355	12%
Site 2	1-Oct-20	3	345	7%

The calculation on excel goes as below (for FY21, i.e. from Sep'20):

YTD % = (Cumulative Sum (Total) / Cumulative Average (HC)) * (12/2) ----- divided by 2 because FY21 starts from Sep'20

Cumulative sum and average would start from Sep'20 as well. So in short if i have to calculate Overall YTD attrition % for both sites the formula would be,

YTD % = (3+1+4+3) / Average (355+355, 347.5 + 345) * (12/2) = 9 %

Also it is done cumulatively so that when we map the monthly attrition it should give us the YTD attrition for that month.

Let me know if i am unclear and need to explain it in a better way.

Looking for help in this.

[Anonymous]

@Anonymous 

Just not clear, regarding to the solution, do you want to create a measure of an overall YTD attrition or just FY 21 Ytd? Can you just show the expected output in a table and the logic?

I don't understand the excel calculation logic, why do you divide by 2 if for FY21 that starts on Sep 20, what about FY20, and FY19? Also, do you calculation site 1 and site 2 together?

Regards
Paul

[Anonymous]

Yes, i want to create a measure for overall YTD attrition %. That way if i map the attrition % for the last 12 months then till Aug'20 it should be calculated basis FY20 and from Sep'20 the calculation should be basis FY21.

For FY21 i have divided by 2 since i have data until Oct'20 which is the 2nd month in FY21. Similarly when we move on to Nov we will divide it by 3 and so on. I need the calculation for both sites, i.e. if both sites are selected it should calculate overall by adding respective numbers and if a single site is selected then calculation should be shown for that site.

I hope i am clear. Let me know if you have any further questions