Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |