Date filter not ignored despite ALL and REMOVEFILTERS

AlexanderMeneik — Fri, 02 Feb 2024 07:31:23 GMT

Hello, with my first question here I had bitten of a lot more that I could chew, so today I am asking about a smaller step in my DAX learning process.

I try to calculate a First Order Date from an Invoices table.

This is my calendar DAX:

DateTable = ADDCOLUMNS(CALENDARAUTO(),
"MonthNo", MONTH([Date]),
"MonthName", FORMAT([Date],"MMMM"),
"Quarter", QUARTER([Date]),

"Year", YEAR([Date]),
"Day", DAY([Date]),
"WeekdayNum", WEEKDAY([Date]),
"WeekdayName",FORMAT([Date],"DDDD"),
"YearMonth", FORMAT([Date], "YYYY-MM"),
"YearQuarter", FORMAT([Date], "YYYY-Q"))

So far, I have tried a few variations. At the moment, I have tried this code:

FirstOrderDate = CALCULATE(MIN('invoices_2019-2022'[Date Initiated]), 
ALL(DateTable[Date]))

That works fine as long as I have not selected any quarter in the slicer.

Yet, as soon as I select a YearQuarter, the results change, and I get the First Order Date in the context of the slicer, despite the ALL function.

Before, I had tried this:

FirstOrderDate = CALCULATE(MIN('invoices_2019-2022'[Date Initiated]), 
REMOVEFILTERS(DateTable[Date]))

Same result.

As you can probably tell, I have about a 5-year-old's underdstanding of DAX, so please explain in a simple way.

Thanks a lot.

Re: Date filter not ignored despite ALL and REMOVEFILTERS

AlexanderMeneik — Fri, 02 Feb 2024 08:03:39 GMT

Looks like the solution was pretty simple - I need to use REMOVEFILTERS on the whole table, not just the [Date] column:

FirstOrderDate = CALCULATE(MIN('invoices_2019-2022'[Date Initiated]),

REMOVEFILTERS(DateTable))

That did the trick.

topic Date filter not ignored despite ALL and REMOVEFILTERS in DAX Commands and Tips

Date filter not ignored despite ALL and REMOVEFILTERS

Re: Date filter not ignored despite ALL and REMOVEFILTERS