Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
------------------------------------------
Solved! Go to 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
)
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.
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
)
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
11 | |
10 | |
10 | |
9 |
User | Count |
---|---|
18 | |
13 | |
12 | |
11 | |
8 |