Issue with EOMONTH Function

[mstone3]

Hello, I am using the following DAX expression, which works as intended with the correct filtering:

Count =

VAR _start = EOMONTH(TODAY(),-4)

VAR _result = CALCULATE(COUNTROWS(Gains),Gains[Effective Date]>=_start)

RETURN _result

However, instead of using the TODAY() function to pass the current date to EOMONTH, I would prefer instead to pass the latest date in the table.  I thought this would be easy by replacing TODAY() with MAX(Gains[Effective Date]:

Count =

VAR _start = EOMONTH(MAX(Gains[Effective Date]),-4)

VAR _result = CALCULATE(COUNTROWS(Gains),Gains[Effective Date]>=_start)

RETURN _result

However, this doesn't work and the results are returned as if no filtering takes place.  I've tried everything I can to troubleshoot, and have ensured that the date column is formatted as a date.

Thanks for your help!

[amustafa · Accepted solution]

When you use `TODAY()`, it's straightforward because it always returns the current date, regardless of any filter context. However, when you use `MAX(Gains[Effective Date])`, the result depends on the current filter context.

To ensure that `MAX` calculates the maximum date over the entire `Gains` table, regardless of any filters that might be applied elsewhere in your report or calculations, you can use the `ALL` function to remove any filters from the `Gains[Effective Date]` column. Here's how you can modify your measure:

Count =
VAR _latestDate = CALCULATE(MAX(Gains[Effective Date]), ALL(Gains))
VAR _start = EOMONTH(_latestDate, -4)
VAR _result = CALCULATE(COUNTROWS(Gains), Gains[Effective Date] >= _start)
RETURN _result

[mstone3]

Brilliant!  This did the trick - thank you so much!