PowerBI Attrition Dashboard for Monthly and yearly

[Nabarun1992]

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

I want the output in the desired format

I saw three date columns in the data set: start date, end date, and date. Can you explain why the date column was used and where was the date column derived from?
Ashish_Mathur

[Ashish_Mathur]

Hi,

Step into the Query Editor to see the transformation steps which i ran on the dataset.

[Nabarun1992]

Hi,
When I put the month-end employees query, it is calculating I one extra employee under each country, not sure why.
Month end employees = CALCULATE([Employee count],DATESBETWEEN('Calendar'[Date],[Date since when data is available],[Date since when data is available]),USERELATIONSHIP('Employee Records'[Hire Date],'Calendar'[Date]))+[Hired till date]-[Left till date]

[Ashish_Mathur]

Hi,

Study my previous file carefully.  If the answers on that file are correct, then the answers on your live file should also be so.  I d not know how else to help you.

[Nabarun1992]

I did, I have put the calculations same as yours. In the month-end employees, it is giving me one extra count. I'm sure in some way you can help me, if you would like to 🙂

[Nabarun1992]

Associate ID	Full Name	Hire Date/Rehire Date	Termination Date
1	Sample, 1	07/18/2011	02/01/2022
2	Sample, 2	09/15/2011
3	Sample, 3	01/01/2008
4	Sample, 4	01/07/2008
5	Sample, 5	01/21/2008
6	Sample, 6	02/20/2008
7	Sample, 7	03/24/2008
8	Sample, 8	03/17/2008
9	Sample, 9	04/09/2008
10	Sample, 10	08/25/2008
11	Sample, 11	04/03/2009
12	Sample, 12	04/20/2009	03/04/2022
13	Sample, 13	04/20/2009
14	Sample, 14	05/11/2009
15	Sample, 15	05/11/2009
16	Sample, 16	08/17/2009
17	Sample, 17	11/09/2009
18	Sample, 18	11/02/2009
19	Sample, 19	01/04/2010
20	Sample, 20	01/04/2010
21	Sample, 21	02/01/2010
22	Sample, 22	05/26/2010
23	Sample, 23	07/06/2010
24	Sample, 24	08/23/2010
25	Sample, 25	10/11/2010
26	Sample, 26	02/21/2011	02/01/2022
27	Sample, 27	03/07/2011
28	Sample, 28	08/29/2011
29	Sample, 29	08/29/2011
30	Sample, 30	09/15/2011
31	Sample, 31	09/15/2011
32	Sample, 32	10/10/2011
33	Sample, 33	11/14/2011	05/13/2022
34	Sample, 34	11/07/2011
35	Sample, 35	06/01/2014
36	Sample, 36	06/01/2014
37	Sample, 37	06/15/2009
38	Sample, 38	10/14/2013
39	Sample, 39	02/09/2009
40	Sample, 40	11/16/2009
41	Sample, 41	10/17/2008
42	Sample, 42	04/12/2010
43	Sample, 43	06/01/2010
44	Sample, 44	07/17/2006
45	Sample, 45	02/21/2011
46	Sample, 46	03/07/2011
47	Sample, 47	04/11/2011
48	Sample, 48	02/14/2007
49	Sample, 49	02/09/2007
50	Sample, 50	11/19/2016
51	Sample, 51	07/11/2022
52	Sample, 52	07/05/2011
53	Sample, 53	01/16/2014
54	Sample, 54	04/26/2019	04/25/2022
55	Sample, 55	03/04/2019
56	Sample, 56	12/15/2016
57	Sample, 57	10/16/2006
58	Sample, 58	04/16/2018
59	Sample, 59	08/11/2022	03/08/2023
60	Sample, 60	02/24/2017
61	Sample, 61	05/26/2015
62	Sample, 62	03/09/2016
63	Sample, 63	07/11/2022
64	Sample, 64	04/13/2016
65	Sample, 65	03/04/2022
66	Sample, 66	11/14/2016
67	Sample, 67	11/14/2018
68	Sample, 68	04/05/2022
69	Sample, 69	08/01/2014	06/01/2022
70	Sample, 70	01/03/2017
71	Sample, 71	09/15/2008
72	Sample, 72	09/21/2016
73	Sample, 73	07/09/2012
74	Sample, 74	04/02/2012
75	Sample, 75	11/30/2022
76	Sample, 76	11/30/2004	11/01/2022
77	Sample, 77	05/01/2020
78	Sample, 78	05/15/2017
79	Sample, 79	10/11/2021	05/10/2022
80	Sample, 80	09/02/2014
81	Sample, 81	12/03/2012
82	Sample, 82	08/16/2010
83	Sample, 83	08/29/2016
84	Sample, 84	10/07/2019
85	Sample, 85	12/21/2018
86	Sample, 86	06/05/2013
87	Sample, 87	08/03/2009
88	Sample, 88	01/18/2021
89	Sample, 89	01/01/2019
90	Sample, 90	11/16/2020	02/09/2022
91	Sample, 91	09/05/2019
92	Sample, 92	02/18/2019
93	Sample, 93	08/07/2000
94	Sample, 94	11/23/2020
95	Sample, 95	08/21/2017
96	Sample, 96	08/06/2018
97	Sample, 97	12/17/2018
98	Sample, 98	01/04/2016
99	Sample, 99	08/01/2002
100	Sample, 100	05/07/2018
101	Sample, 101	06/10/2019
102	Sample, 102	04/07/2019
103	Sample, 103	06/20/2017
104	Sample, 104	05/15/2017
105	Sample, 105	03/01/2017
106	Sample, 106	04/15/2019
107	Sample, 107	10/01/2018
108	Sample, 108	05/07/2018
109	Sample, 109	09/30/2019
110	Sample, 110	05/14/2021
111	Sample, 111	04/18/2019

@Ashish_Mathur 

[v-shex-msft]

Hi @Nabarun1992,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Nabarun1992]

Referring to https://community.powerbi.com/t5/Desktop/PowerBI-Attrition-Dashboard-for-Monthly-and-yearly/m-p/2707...