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