Month on Month YTD Attrition

[Anonymous]

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

[LivioLanzo · Accepted 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] ) )
        )
    )
)