Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
LivioLanzo
Solution Sage
Solution Sage

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"

 

 

 

 

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
Solution Sage
Solution Sage

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"

 

 

 

 

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!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.