cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Month on Month YTD Attrition

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 IDYear tagMonth tagTagExit TagHC Tag
12018AugHC01
22018AugHC01
32018AugHC01
42018AugHC01
52018AugHC01
12018SepHC01
22018SepEx10
32018SepHC01
42018SepHC01
52018SepHC01
12018OctEx10
22018OctEx10
32018OctHC01
42018OctHC01
52018OctHC01
62018OctHC01
12018NovEx10
22018NovEx10
32018NovHC01
42018NovHC01
52018NovEx10
62018NovHC01
72018NovHC01



1 ACCEPTED SOLUTION

Accepted Solutions
LivioLanzo Super Contributor
Super Contributor

Re: Month on Month YTD Attrition

Hello @Chetanrajhans,

 

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"

 

 

 

 

2018-11-27_8-42-31.jpg

 

 

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 )
)

2018-11-27_8-43-34.jpg

 

2018-11-27_8-44-35.jpg2018-11-27_8-45-05.jpg

 

 

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!  

View solution in original post

1 REPLY 1
LivioLanzo Super Contributor
Super Contributor

Re: Month on Month YTD Attrition

Hello @Chetanrajhans,

 

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"

 

 

 

 

2018-11-27_8-42-31.jpg

 

 

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 )
)

2018-11-27_8-43-34.jpg

 

2018-11-27_8-44-35.jpg2018-11-27_8-45-05.jpg

 

 

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!  

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 121 members 1,239 guests
Please welcome our newest community members: