Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

date filtering

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

)

)

)

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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))

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.