Forum Discussion
Help needed with filters
Hello, For background - the hire date field has no relationship with the date table. I need to count employees that were hired in the past year based on the date selected - but I need to remov...
Perhaps the FILTER is being called before the REMOVEFILTERS is applied. Try
TTM Hires = VAR selecteddate = MAX ( 'Date'[Date] ) VAR enddate = DATEADD ( LASTDATE ( 'Date'[Date] ), -1, YEAR ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Monthly Workforce'[FDS ID] ), REMOVEFILTERS ( 'Date' ), 'Monthly Workforce'[Hire Date] <= selecteddate && 'Monthly Workforce'[Hire Date] > enddate )
This really has me puzzled. I am quite the beginner when it comes to DAX - but I thought what I was doing might work 😋
Thank you - but the change did not work.
I was able to get the correct answer by creating a calculated table that summarizes the Workforce table by month and then filtering for the needed timeframe. But I am really curious as to why I can't get the other formula to work.
johnt75
Super User
Super UserPerhaps the FILTER is being called before the REMOVEFILTERS is applied. Try
TTM Hires =
VAR selecteddate =
MAX ( 'Date'[Date] )
VAR enddate =
DATEADD ( LASTDATE ( 'Date'[Date] ), -1, YEAR )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Monthly Workforce'[FDS ID] ),
REMOVEFILTERS ( 'Date' ),
'Monthly Workforce'[Hire Date] <= selecteddate
&& 'Monthly Workforce'[Hire Date] > enddate
)Thank you so much. This is such a long journey. (Learning DAX)
- johnt75
If you can, take the SQL BI guys course on DAX, and definitely check them out on YouTube.
Enjoy DAX!