topic Re: display only the last n months of a rolling multi year total while still being able to filter in DAX Commands and Tips

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

Anonymous — Tue, 31 May 2022 03:05:43 GMT

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

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

tamerj1 — Tue, 31 May 2022 04:45:04 GMT

Hi Anonymous
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

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

Anonymous — Tue, 31 May 2022 05:51:01 GMT

OMG! amazing thankyou so much!!!