Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am trying to create a measure which calculates employee attrition/turnover rate.
Employee Attrition / Turnover Rate ( YTD) = Sum of all Exits till that date/Avg(Avg(Headcount of each month in period))
I have my data in below format which has the month on month data for each year for all employees with a tag( Exit / Headcount),
Is there a way to create such a measure in Power Bi, I have tried Total YTD function to get a cumulative total but as I don't have a date field in my data to it's not giving correct O/P
Employee ID | Year tag | Month tag | Tag | Exit Tag | HC Tag |
1 | 2018 | Aug | HC | 0 | 1 |
2 | 2018 | Aug | HC | 0 | 1 |
3 | 2018 | Aug | HC | 0 | 1 |
4 | 2018 | Aug | HC | 0 | 1 |
5 | 2018 | Aug | HC | 0 | 1 |
1 | 2018 | Sep | HC | 0 | 1 |
2 | 2018 | Sep | Ex | 1 | 0 |
3 | 2018 | Sep | HC | 0 | 1 |
4 | 2018 | Sep | HC | 0 | 1 |
5 | 2018 | Sep | HC | 0 | 1 |
1 | 2018 | Oct | Ex | 1 | 0 |
2 | 2018 | Oct | Ex | 1 | 0 |
3 | 2018 | Oct | HC | 0 | 1 |
4 | 2018 | Oct | HC | 0 | 1 |
5 | 2018 | Oct | HC | 0 | 1 |
6 | 2018 | Oct | HC | 0 | 1 |
1 | 2018 | Nov | Ex | 1 | 0 |
2 | 2018 | Nov | Ex | 1 | 0 |
3 | 2018 | Nov | HC | 0 | 1 |
4 | 2018 | Nov | HC | 0 | 1 |
5 | 2018 | Nov | Ex | 1 | 0 |
6 | 2018 | Nov | HC | 0 | 1 |
7 | 2018 | Nov | HC | 0 | 1 |
Solved! Go to Solution.
Hello @Anonymous,
to get a YTD measure, first you need to build a dimension such as this one:
MonthlyCalendar = SELECTCOLUMNS( ADDCOLUMNS( GENERATESERIES( 1, 24 ), "Date", EDATE( DATE( 2018, 1, 1 ), [Value] - 1 ) ), "Year Month", FORMAT( [Date], "mmm yyyy" ), "Year Month Number", YEAR( [Date] ) * 100 + MONTH( [Date] ), "Year", YEAR( [Date] ), "Month", MONTH( [Date] ), "Month Name", FORMAT( [Date], "mmm" ) )
This will build the below "Calendar"
Afterwards, you need to add the YearMonth Number in your original Table:
DataModified = SELECTCOLUMNS( Data, "Employee ID", [Employee ID], "Tag", [Tag], "Exit Tag", [Exit Tag], "HC Tag", [HC Tag], "Year Month Number", VAR iDate = DATEVALUE( "1-" & [Month Name] & "-" & [Year] ) RETURN YEAR( iDate ) * 100 + MONTH( iDate ) )
The measure to add is:
YTD Exits = IF( MIN( MonthlyCalendar[Year Month Number] ) > CALCULATE( MAX( DataModified[Year Month Number] ), ALL( DataModified ) ), BLANK(), CALCULATE( SUM( DataModified[Exit Tag] ), FILTER( ALL( MonthlyCalendar ), AND( MonthlyCalendar[Year] = MAX( MonthlyCalendar[Year] ), MonthlyCalendar[Month] <= MAX( MonthlyCalendar[Month] ) ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hello @Anonymous,
to get a YTD measure, first you need to build a dimension such as this one:
MonthlyCalendar = SELECTCOLUMNS( ADDCOLUMNS( GENERATESERIES( 1, 24 ), "Date", EDATE( DATE( 2018, 1, 1 ), [Value] - 1 ) ), "Year Month", FORMAT( [Date], "mmm yyyy" ), "Year Month Number", YEAR( [Date] ) * 100 + MONTH( [Date] ), "Year", YEAR( [Date] ), "Month", MONTH( [Date] ), "Month Name", FORMAT( [Date], "mmm" ) )
This will build the below "Calendar"
Afterwards, you need to add the YearMonth Number in your original Table:
DataModified = SELECTCOLUMNS( Data, "Employee ID", [Employee ID], "Tag", [Tag], "Exit Tag", [Exit Tag], "HC Tag", [HC Tag], "Year Month Number", VAR iDate = DATEVALUE( "1-" & [Month Name] & "-" & [Year] ) RETURN YEAR( iDate ) * 100 + MONTH( iDate ) )
The measure to add is:
YTD Exits = IF( MIN( MonthlyCalendar[Year Month Number] ) > CALCULATE( MAX( DataModified[Year Month Number] ), ALL( DataModified ) ), BLANK(), CALCULATE( SUM( DataModified[Exit Tag] ), FILTER( ALL( MonthlyCalendar ), AND( MonthlyCalendar[Year] = MAX( MonthlyCalendar[Year] ), MonthlyCalendar[Month] <= MAX( MonthlyCalendar[Month] ) ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |