Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Annual Attrition% Calculation

Hi All,

 

I wanted to calculate Annulised Attrition% as mentioned below:

Requirement.JPG

 

 

 

 

 

 

 

 

Here are the excel formula's used to arrive the % and numbers.

Annualised Attrition% : (E5/MONTH(A5)*12)/((F4+F4)/2)

YTD Leavers                 : B5, E5+B6, …..

Headcount                  : COUNTA(MDB!A:A)-1-COUNTIF(MDB!B:B,">="&A4)-COUNTIF(MDB!$C:$C,"<"&A4)

 

Link to sample data: https://drive.google.com/file/d/1QrL3JvPDh_WgvvVWxiSTtokNKQBetLNb/view?usp=sharing

 

I am able to create Headcount and YTD Leavers measures as given below:

Headcount = [Total Count - ALL] - 1 - [Total Join - After] - ([Total Left before] - [Total Left])
YTD = TOTALYTD([Total Left],'Date'[Date])
However headcount numbers are not matching with the excel results and unable to get Annualised Attrition %.
 
Any help on this is much appreciated.
 
Regards,
Pavan
5 REPLIES 5
v-stephen-msft
Community Support
Community Support

Hi @Anonymous ,

 

I expanded each item of HeadCount and got the following results.

22.png23.png

 

[Total Count - ALL] should be 1935. An extra line was added in Excel.

24.png25.png

 

Another problem lies in [Total Join-After], I want to know what is the logic of this.

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi @v-stephen-msft ,

 

The total count in excel, it is taking header as well, hence we used -1 to get 1935.

Total Join After is the count of new joiners after the month given on the left side of the table.

Hope this clarified your query.

 

Regards,

Pavan

Hi @Anonymous ,

 

Then there is no need to subtract one in pbix, because it doesn't count the header.

1.png

It should be like this

Headcount = 
[Total Count - ALL] - [Total Join - After] - ([Total Left before] - [Total Left])

 

MAX('Date'[Date]) in Total Join-After returns the largest day of the currently selected month. as the picture shows. So remove the equal sign in Total Join-After.

3.png

Total Join - After = 
CALCULATE ( [Count], FILTER ( ALL ( 'Date' ), 'Date'[Date] > MAX('Date'[Date])))

4.png

 

The number of newly-added employees after December 31, 2020 should be 0, so I think it may be that the formula in your Excel is wrong. 186 should be the number of new employees joining after November 30, 2020.

5.png

 

Hope my explanation can help you.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Hi @v-stephen-msft ,

 

I would like to know how can we achieve the annualised attrition % as mentioned in the excel with the existing data.

 

Regards,

Pavan

Anonymous
Not applicable

Hi All,

 

Does anyone got a chance to look into it and suggest.

 

Regards,

Pavan

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.