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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Chandra_maha
Helper I
Helper I

PowerBI Attrition Dashboard for Monthly and yearly

Hi All,

 

I am trying to create monthly/Yearly PowerBI attrition. Following is data. 

 

Attrition = Total No of employee left for month/No of average employee for month*100

Number of average employee = no of employee at the start of the month + no of employee at the end of the month/2

 

Chandra_maha_0-1660648882287.png

The desire output is 

Chandra_maha_1-1660648925498.png

Thank you!

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

19 REPLIES 19
TeeJay80
Advocate I
Advocate I

Hi @Chandra_maha ,

 

I have to do a similar task that you did here. I'm trying to find a PBI temlate re HR attrition. Do you still have this file, so I could use it with my data?

Arv111
Regular Visitor

Hi Ashish,

In addition to what you have provided, I am looking to create attrition dashboard which could be filtered by department slicer. How can this be achieved?

Thanks,

Arv

Just create a slicer of the Department column.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

Share data in a format that can be pasted in an MS Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi, 

Employee Id NameStart DateEnd Date
1A01-Jan-15 
2B01-Jan-15 
3C10-Feb-1531-Dec-15
4D12-Mar-15 
5E15-Apr-1531-May-17
6F15-Apr-1530-Apr-18
7G15-Jun-1531-Oct-15
8H20-Jun-1505-Feb-19
9I20-Sep-1510-Mar-19
10J12-Oct-1530-Sep-16
11K05-Jan-16 
12L05-Feb-16 
13M13-Mar-1731-Mar-17
14N20-Mar-1712-Apr-19
15O20-May-1710-Feb-18
16P10-Jan-1810-Feb-18
17Q20-May-18 
18R20-Mar-19 
19S13-May-19 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Chandra_maha , in the data table there is a date column, may I know from where is it derived?

And, also in the matrix visualization, on left under 2015, month start employees, hired, left, ... how was that built? Can you please help?

Hi @Ashish_Mathur Can you help me on this?

Please start a new thread.  Explain your question in detail there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello Ashish,

That was the great solution for which I was looking for.  Also, I want to undestand, If I want to calulate the Attrition for every previous 06 month then, how can I do that? 

In your DAX formula, you have used "Previous month" function to get the value of last month, how that can be done if, I want to take the value of last 06th month 

Use the DATESBETWEEN() function.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you for that. Actually I am trying to create graph. As shown below

Chandra_maha_0-1662372857637.png

Blue one - Regular month on month Attrition graph. Which we have created already
Red one (Need to create) - Attrition graph for 6 month.
Example - Suppose I am click June then it will give me the attrition percentage of last 06(Jan to June) month and it will be go on.
Suppose I click on Aug then it be from march to Aug. I am trying to get that. Can you help me on this.
You can consider the same data set. 

I may not be able to devote so much time to solving this one.  May be someone will help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank, you have helped me a lot 🙂

Dhacd
Resolver III
Resolver III

@Chandra_maha , can you give clarity on number of average employees?

Ex -

Chandra_maha_0-1660651532781.png

As you can in above January table -  at the start of month No. of employee is 1. This is "no of employee at the start of the month"
And if you add all the employee joined in that month then its 32. This is "No of employee at the end of the month".

Hi, could you please upload the attrition file, so I could use it for my data? Thanks in advance!

Should we add all the employees who joined in January and subtract those who left the company to find the no of employees at the end of the month?

and what if December had about 15 employees joined, Should we consider them for the no of employees at start of the month for Jan? 

Hello,
Yes, we need to conside as you mention. I have created table for explanation 

Month Hired EmployeeLeft EmployeeStart of the MonthEnd of the Month
Dec'142002
Jan'151023
Feb'152035
March'151253
April'154136

 

We get "End of Month Employee" count by adding "Hired Employee" and subtracting "Left employee". 
Also, last month "End of month Employee" (Dec'14 = 2) would be "Start of the month employee" count (Jan'15) = 2

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors