Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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


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

View solution in original post

5 REPLIES 5
johnt75
Super User
Super User

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
Not applicable

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.

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
Not applicable

Thank you so much.  This is such a long journey.  (Learning DAX)

If you can, take the SQL BI guys course on DAX, and definitely check them out on YouTube.

Enjoy DAX!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.