Forum Discussion
Issue with EOMONTH Function
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
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
Brilliant! This did the trick - thank you so much!