cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Egemeny
Frequent Visitor

Employee Retention, Filtering Based on Job Title and Business Location

Hi everyone,

 

Thanks to this group I managed to work on my dax to calculate retention % for the business. However, the problem that I'm experiencing is when I tried to filter my visualization based on Job Title or Business Unit code, my measure doesn't change in line with the filter.

My aim is to see retention % based on different business units and job title's so we can identify possible challenges for retaining talent within the organization.

 

Here is my DAX:

Thank you so much for the support!

Retention % = 
VAR _Previous12START =
    EOMONTH ( MAX ( 'Calendar'[Date] ), -13 ) + 1
VAR _Previous12END =
    EOMONTH ( MAX ( 'Calendar'[Date] ), -1 )
VAR _LEFT =
    CALCULATE (
        COUNT ( 'Master Data'[Employee Code] ),
        FILTER (
            ALL ( 'Master Data' ),
            'Master Data'[Date Engaged] >= _Previous12START
                && 'Master Data'[Date Engaged] <= _Previous12END
                && 'Master Data'[Date of Resignation] >= _Previous12START
                && 'Master Data'[Date of Resignation] <= _Previous12END
        )
    ) + 0
VAR _Total =
    CALCULATE (
        COUNT ( 'Master Data'[Employee Code] ),
        FILTER (
            ALL ( 'Master Data' ),
            'Master Data'[Date Engaged] <= _Previous12END
                && OR (
                    'Master Data'[Date of Resignation] >= _Previous12START,
                    'Master Data'[Date of Resignation] = BLANK ()
                )
        )
    )
RETURN
    1 - DIVIDE ( _LEFT, _Total )

 

This is how the visualization look like:

Retention.JPG

 

 

1 ACCEPTED SOLUTION
Shaurya
Memorable Member
Memorable Member

Hi @Egemeny,

 

You are using Filter All which is why your measure is not working in the context of your slicers. Removing all should do the trick.

 

Retention % = 
VAR _Previous12START =
    EOMONTH (MAX('Calendar'[Date]),-13)+1
VAR _Previous12END =
    EOMONTH (MAX('Calendar'[Date]),-1 )

VAR _LEFT =
    CALCULATE (
        COUNT('Master Data'[Employee Code]),
        FILTER('Master Data',
            'Master Data'[Date Engaged] >= _Previous12START
                && 'Master Data'[Date Engaged] <= _Previous12END
                && 'Master Data'[Date of Resignation] >= _Previous12START
                && 'Master Data'[Date of Resignation] <= _Previous12END
        )
    ) + 0

VAR _Total =
    CALCULATE(
        COUNT('Master Data'[Employee Code]),
        FILTER('Master Data',
            'Master Data'[Date Engaged] <= _Previous12END
                && OR (
                    'Master Data'[Date of Resignation] >= _Previous12START,
                    'Master Data'[Date of Resignation] = BLANK ()
                )
        )
    )
RETURN
    1 - DIVIDE(_LEFT, _Total)

 

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Graphical Comparison 

View solution in original post

1 REPLY 1
Shaurya
Memorable Member
Memorable Member

Hi @Egemeny,

 

You are using Filter All which is why your measure is not working in the context of your slicers. Removing all should do the trick.

 

Retention % = 
VAR _Previous12START =
    EOMONTH (MAX('Calendar'[Date]),-13)+1
VAR _Previous12END =
    EOMONTH (MAX('Calendar'[Date]),-1 )

VAR _LEFT =
    CALCULATE (
        COUNT('Master Data'[Employee Code]),
        FILTER('Master Data',
            'Master Data'[Date Engaged] >= _Previous12START
                && 'Master Data'[Date Engaged] <= _Previous12END
                && 'Master Data'[Date of Resignation] >= _Previous12START
                && 'Master Data'[Date of Resignation] <= _Previous12END
        )
    ) + 0

VAR _Total =
    CALCULATE(
        COUNT('Master Data'[Employee Code]),
        FILTER('Master Data',
            'Master Data'[Date Engaged] <= _Previous12END
                && OR (
                    'Master Data'[Date of Resignation] >= _Previous12START,
                    'Master Data'[Date of Resignation] = BLANK ()
                )
        )
    )
RETURN
    1 - DIVIDE(_LEFT, _Total)

 

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Graphical Comparison 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors