Employee Retention, Filtering Based on Job Title and Business Location

[Egemeny]

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:

[Shaurya · Accepted solution]

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