Help needed with filters

[Anonymous]

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 remove the filter from the table so I can get all the employees.

Here is what I am trying - but the filter is still in place

------------------------------------------

TTM Hires =

Var selecteddate = max('Date'[Date])

Var enddate = dateadd(lastdate('Date'[Date]),-1,Year)

return

Calculate (DISTINCTCOUNT('Monthly Workforce'[FDS ID]),

REMOVEFILTERS('Date'[Date]),

Filter('Monthly Workforce',

AND(

'Monthly Workforce'[Hire Date] <= selecteddate ,

'Monthly Workforce'[Hire Date]>enddate)

))

[johnt75]

Try changing the REMOVEFILTERS('Date'[Date]) to REMOVEFILTERS('Date'), so that filters on any column in the date table are removed. Its common with date tables for other filter columns to be added behind the scenes, e.g. a month sort order column.

[Anonymous]

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 · Accepted solution]

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
    )

[Anonymous]

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!