display only the last n months of a rolling multi year total while still being able to filter

[thisguy]

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: 

Employee Count =

VAR started =

CALCULATE (

COUNT ( 'FACT_Data'[Start/Finish] ),

'FACT_Data'[Start/Finish] = "Employment Date",

FILTER (

ALLSELECTED ( 'FACT_Data' ),

'FACT_Data'[Date] <= MAX ( DIM_Calendar[Date] )

),all(DIM_Calendar[Date])

)

VAR ended =

CALCULATE (

COUNT ( 'FACT_ Data'[Start/Finish] ),

'FACT_Data'[Start/Finish] = "Termination Date",

FILTER (

ALLSELECTED ( 'FACT_Data' ),

'FACT_ Data'[Date] <= MAX ( DIM_Calendar[Date] )

),all(DIM_Calendar[Date])

)

RETURN

started-ended

[tamerj1 · Accepted 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

[thisguy]

OMG!   amazing thankyou so much!!!