Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |