Forum Discussion
Issue with EOMONTH Function
Hello, I am using the following DAX expression, which works as intended with the correct filtering:
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
2 Replies
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