Thank you for clarifying, @Petja .
DATESBETWEEN() loses its dynamic nature when both its start and end dates are set outside the current filter context, such as when using ALL() or REMOVEFILTERS() on the Calendar before MIN/MAX. This causes the same full-year range to be applied to every row, so each month shows the same annual total. To keep things dynamic and handle a few days of new-year data lag, you can either calculate an effective end date for each row and build YTD based on that year, or continue with DATESYTD() and shift the context back by the lag. Both options let the range adjust per row and maintain last year’s running total during the first days of the new year.
For more control, you can use this effective end YTD approach:
[Distinct Keys] =
DISTINCTCOUNTNOBLANK('Fact'[Key])
[YTD (Lag-Aware, Explicit End)] =
VAR LagDays = 5 - adjust as needed
VAR ContextEnd = MAX('Calendar'[Date])
VAR LastFactDate = CALCULATE(MAX('Fact'[Date]), ALL('Fact'))
VAR EffectiveEnd = MIN(ContextEnd, LastFactDate) - LagDays
VAR EffectiveStart = DATE(YEAR(EffectiveEnd), 1, 1)
RETURN
CALCULATE(
[Distinct Keys],
DATESBETWEEN('Calendar'[Date], EffectiveStart, EffectiveEnd)
)
This measure stays dynamic since ContextEnd uses the current row context. At the start of January, if there’s a data lag, EffectiveEnd stays in the previous year, so the measure continues last year’s YTD until the lag is over.
If your Date table is continuous and set up as a Date table, you can also use a concise method by shifting the context with the lag and still using DATESYTD():
[YTD (Lag-Aware, DATEADD Shift)] =
VAR LagDays = 5
RETURN
CALCULATE(
[Distinct Keys],
DATESYTD('Calendar'[Date]),
DATEADD('Calendar'[Date], -LagDays, DAY)
)
This approach returns the year-to-date value as of the current date minus LagDays, so for the first few days of the year, last year’s totals will be shown.
