Forum Discussion
ISO Calendar YTD calculation with Variable
Hi All,
I have a model on a 5-4-4 calendar and have implemented the Week-Based Time Intelligence pattern (https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/) to replicate the standard YTD, MTD, time-based aggregations.
However, I also have a requirement to have the measures default to be filtered by a specific date, but that the any addition of filters to a report should override the filter condition in the measure. I've been able to accomplish this by using a variable in the expression.
However, when the ALL 'Date' filter is included in the measure in order to perform the time based aggregation, the variable filter is no longer enforced.
Here's an example of the YTD measure:
YTD Sales = VAR MAXDATE =
IF (ISFILTERED('Date'[IsoWeekYear]) || ISFILTERED('Date'[Period544]) || ISFILTERED('Date'[DateInt]), MAX('Date'[DateInt]), MAX('Filter Date'[Last Date]))
RETURN CALCULATE (
SUM('Sales'[Net Sales]),
FILTER (ALL ('Date'), 'Date'[ISOWeekYear] = MAX ( 'Date'[ISOWeekYear]) && 'Date'[DateInt] <= MAXDATE
))
What would be the best way to avoid using FILTER (ALL ('Date') and still have a YTD calculation? Thanks!
- Hi there have you tried changing the ALL to ALLSELECTED?
That should solve your issue.