Annualized Attrition Calculation

[Anonymous]

Hello Power BI Fam!

I have the following dataset:

I need to calculate the Annualized Attrition formula for which is as follows:

Annualized Attrition (any month, any Financial Year FY)=(No of exits till that month from April for that FY/Headcount on 31st March of Last FY)*(12/Month no. of that month)

I need to plot the above in Graphs with Monthname as x axis, Business Unit as x axis or Function Name as x axis and Financial Year FY as legend.

Any Financial Year FY starts from April, so month no is 1 for April and so on!

Requesting urgent help in solving the above!

@amitchandak @parry2k @AlB @mahoneypat @AllisonKennedy @lbendlin @Fowmy @MFelix @collinq @kevhav 

@Pragati11 @littlemojopuppy @StefanoGrimaldi @aj1973 @Ashish_Mathur 

[StefanoGrimaldi]

let me get this right, basically you need to know the proportion of people that leave monthly, example: 

february leave 5, january total head count on 31 its 10, % Attrition on febreary = 50% ? 

[StefanoGrimaldi]

hey, 

if you can share a dummy data with those columns would be easier to assit. 

[Anonymous]

Hi @StefanoGrimaldi Here's the dummy data

https://drive.google.com/file/d/1gysPnuZyu67-Z-q6E9a8O3zYGGiUALtR/view?usp=sharing 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Also, share the data in an MS Excel file format.

[Anonymous]

Hello @Ashish_Mathur !

I havent yet prepared a PBIX file. Attched is the excel link

https://drive.google.com/file/d/1gysPnuZyu67-Z-q6E9a8O3zYGGiUALtR/view?usp=sharing 

[Ashish_Mathur · Accepted solution]

Hi,

I hve done some work.  This should get you started.  Download the PBI file from here.

Hope this helps.

[Anonymous]

Hello @Ashish_Mathur , the Churn YTD is one less than what should be from the data, for every month. Not being able to figure out why. something to do with the 'status' custom column i guess?