date filtering

[Anonymous]

I am trying to figure out why the formula below fail to calculate number of employees leaving the organization during the past 12 months. The formula seems to stop working sometime in 2020. The rolling 12 months exit number reported for 2020 Dec (i.e., # leavers from 2021-1-1 till 2021-12-31) is only 572. This is quite low comparing with # leavers (# of leavers during the month).  

Rolling 12 Months #Exits =

VAR Maxdate =

MAX ( 'Calendar'[Date] )

RETURN

CALCULATE (

COUNTROWS ( Headcount ),

USERELATIONSHIP ( 'Calendar'[Date], Headcount[Exit Date] ),

NOT ISBLANK ( Headcount[Exit Date] ),

FILTER (

ALL ( 'Calendar'[Date] ),

AND (

'Calendar'[Date] <= Maxdate,

DATEADD ( 'Calendar'[Date], 1, YEAR ) > Maxdate

)

)

)

[amitchandak · Accepted solution]

@Anonymous , Try like 

CALCULATE (
CALCULATE (
	COUNTROWS ( Headcount ),
	USERELATIONSHIP ( 'Calendar'[Date], Headcount[Exit Date] ),
	NOT ISBLANK ( Headcount[Exit Date] )),,DATESINPERIOD('Calendar'[Date ],MAX('Calendar'[Date ]),-12,MONTH))  

[Anonymous]

Thanks! Your solution should work. My measure failed because the dateadd() part doesn't work properly. My celandar[Date] ends at 2021-03-01, that means if the date context is 2020-04-01, the dateadd('Calendar'[Date], 1, YEAR ) function will not work because it exceeds the max date in the calendar.