Calcuating Attrition Data by Month

[kc08h]

I have separate cards displaying the number of employees who started this month (let's say September) (StartDate)

Under that is the number of employees who left this month (let's say September) (OffboardDate)

Under that I need to calculate the net values/count of those two numbers for a particular month (Count of StartDate - Count of OffboardDate)

The problem I'm running into is that I can't simply filter by month of onboarding/offboarding. I need to filter by general month. E.g., show me the net value of all onboard and offboards for September. (Count of StartDate - Count of OffboardDate for September) It seems really simple but not seeing how to get it displayed with just a general month.

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope you can start from here to come up with a solution for your semantic model.

employee count measure: =
VAR _t =
    FILTER (
        employee,
        OR (
            employee[offboard_date] >= MIN ( 'calendar'[Date] ),
            employee[offboard_date] = BLANK ()
        )
            && employee[start_date] <= MAX ( 'calendar'[Date] )
    )
RETURN
    IF ( MIN ( 'calendar'[Date] ) <= TODAY (), COUNTROWS ( _t ) )

[dharmendars007]

Hello @kc08h , 

Providing sample data will help..untill that you can try the below approach this should work...

Step1 

First, ensure you have month columns in your data model for both StartDate (onboarding) and OffboardDate (offboarding) to filter by month.

StartMonth = FORMAT([StartDate], "MMMM YYYY")
OffboardMonth = FORMAT([OffboardDate], "MMMM YYYY")

Step2 

Create a Meaure to calculate the Onboarding and Offboarding based on the selected month that is your filter.

OnboardingCount =
CALCULATE(
COUNTROWS(Table),
FILTER(Table, FORMAT([StartDate], "MMMM YYYY") = SELECTEDVALUE('Calendar'[MonthYear])))

OffboardingCount =
CALCULATE(
COUNTROWS(Table),
FILTER(Table, FORMAT([OffboardDate], "MMMM YYYY") = SELECTEDVALUE('Calendar'[MonthYear])))

Step 3

Create a third measure to calculate the net attrition (onboardings minus offboardings)

NetAttrition = [OnboardingCount] - [OffboardingCount]

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN