PowerBI Attrition Dashboard for Monthly and yearly

[Chandra_maha]

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

The desire output is 

Thank you!

[TeeJay80]

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]

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

[Ashish_Mathur]

Just create a slicer of the Department column.

[Ashish_Mathur]

Hi,

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

[Chandra_maha]

Hi, 

Employee Id	Name	Start Date	End Date
1	A	01-Jan-15
2	B	01-Jan-15
3	C	10-Feb-15	31-Dec-15
4	D	12-Mar-15
5	E	15-Apr-15	31-May-17
6	F	15-Apr-15	30-Apr-18
7	G	15-Jun-15	31-Oct-15
8	H	20-Jun-15	05-Feb-19
9	I	20-Sep-15	10-Mar-19
10	J	12-Oct-15	30-Sep-16
11	K	05-Jan-16
12	L	05-Feb-16
13	M	13-Mar-17	31-Mar-17
14	N	20-Mar-17	12-Apr-19
15	O	20-May-17	10-Feb-18
16	P	10-Jan-18	10-Feb-18
17	Q	20-May-18
18	R	20-Mar-19
19	S	13-May-19

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Nabarun1992]

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?

[Nabarun1992]

Hi @Ashish_Mathur Can you help me on this?

[Ashish_Mathur]

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

[Chandra_maha]

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 

[Ashish_Mathur]

Use the DATESBETWEEN() function.

[Chandra_maha]

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

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. 

[Ashish_Mathur]

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

[Chandra_maha]

Thank, you have helped me a lot 🙂

[Dhacd]

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

[Chandra_maha]

Ex -

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".

[TeeJay80]

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

[Dhacd]

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? 

[Chandra_maha]

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

Month	Hired Employee	Left Employee	Start of the Month	End of the Month
Dec'14	2	0	0	2
Jan'15	1	0	2	3
Feb'15	2	0	3	5
March'15	1	2	5	3
April'15	4	1	3	6

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