Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need to be able to show the last 6 months headcount of the full org and be able to filter by department. I am having trouble working out the correct DAX.
in the measure below If I use ALL then when a slicer is selected it will not filter by department, if i use ALLSELECTED it does not show the correct count which only shows the total of the last 6 months (filter on visual) starts - finishes.
I have the following FACT_Data table structure:
EMPID Start/Finish Date DEPT
0322 | Employment Date | 7/03/2020 | 1 |
0366 | Employment Date | 19/04/2021 | 1 |
1043 | Employment Date | 7/03/2020 | 1 |
1044 | Employment Date | 1/03/2022 | 2 |
1177 | Employment Date | 11/02/2022 | 2 |
1232 | Employment Date | 13/05/2021 | 2 |
1239 | Employment Date | 27/04/2020 | 1 |
1239 | Termination Date | 10/07/2020 | 1 |
1369 | Employment Date | 4/01/2021 | 2 |
1369 | Termination Date | 26/02/2021 | 2 |
I have a measure that gives a rolling headcount:
Solved! Go to Solution.
Hi @thisguy
The following seems to work fine
Employee Count =
VAR CurrentDate = MAX ( Dim_Calendar[Date] )
VAR SelectedData = ALLSELECTED ( FACT_Data )
VAR EmpData = FILTER ( SelectedData, FACT_Data[Start/Finish] = "Employment Date" )
VAR TerData = FILTER ( SelectedData, FACT_Data[Start/Finish] = "Termination Date" )
VAR Employed = COUNTROWS ( FILTER ( EmpData, FACT_Data[ Date ] <= CurrentDate ) )
VAR Terminated = COUNTROWS ( FILTER ( TerData, FACT_Data[ Date ] <= CurrentDate ) )
RETURN
Employed - Terminated
Hi @thisguy
The following seems to work fine
Employee Count =
VAR CurrentDate = MAX ( Dim_Calendar[Date] )
VAR SelectedData = ALLSELECTED ( FACT_Data )
VAR EmpData = FILTER ( SelectedData, FACT_Data[Start/Finish] = "Employment Date" )
VAR TerData = FILTER ( SelectedData, FACT_Data[Start/Finish] = "Termination Date" )
VAR Employed = COUNTROWS ( FILTER ( EmpData, FACT_Data[ Date ] <= CurrentDate ) )
VAR Terminated = COUNTROWS ( FILTER ( TerData, FACT_Data[ Date ] <= CurrentDate ) )
RETURN
Employed - Terminated
OMG! amazing thankyou so much!!!
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |