topic Re: Difference between DATESYTD() and DATESBETWEEN() in DAX Commands and Tips

Difference between DATESYTD() and DATESBETWEEN()

Petja — Fri, 03 Oct 2025 08:46:43 GMT

Hi,

I'm trying to figure out how PBI handles these commands differently. I can get a simple monthly cumulative sum of distinct counts by using:

CALCULATE(DISTINCTCOUNTNOBLANK('Table'[Key]),DATESYTD(Calendar[Date]))

and adding this to a visual with months (from the Calendar table) on another axis.

If I change the DATESYTD to DATESBETWEEN I only get the yearly total for each month. If I run DATESYTD() and DATESBETWEEN() in Dax studio, I get the same table, so what changes here?

Re: Difference between DATESYTD() and DATESBETWEEN()

johnt75 — Fri, 03 Oct 2025 10:53:33 GMT

What is the code for the version where you are using DATESBETWEEN ?

Re: Difference between DATESYTD() and DATESBETWEEN()

v-sshirivolu — Wed, 08 Oct 2025 10:22:50 GMT

Hi Petja ,

Thank you for your question on the Microsoft Fabric Community Forum.

To clarify the difference between DATESYTD() and DATESBETWEEN() in Power BI:

Difference - DATESYTD(<dates>) : Returns a dynamic set of dates from the start of the year up to the last date in the current filter context, automatically adjusting based on your visual (e.g., month on the axis).

DATESBETWEEN(<dates>, <start_date>, <end_date>) : Returns a static set of dates between specified start and end dates and does not adjust automatically unless you make it dynamic.

Different Results, Using DATESYTD(Calendar[Date]) with months on the x-axis provides a cumulative total by month (e.g., January = Jan, February = Jan + Feb, etc.).Using DATESBETWEEN(Calendar[Date], DATE(2025,1,1), DATE(2025,12,31)) returns the same yearly total for each month, as the range is fixed.

Making DATESBETWEEN() Dynamic:

You can make DATESBETWEEN() cumulative by using:
CALCULATE(
DISTINCTCOUNTNOBLANK('Table'[Key]),
DATESBETWEEN(
Calendar[Date],
DATE(YEAR(MIN(Calendar[Date])), 1, 1), -- Start of the year
MAX(Calendar[Date]) -- Current context end date
)
)

MIN(Calendar[Date]) and MAX(Calendar[Date]) ensure the date range adjusts dynamically for each row in your visual, matching DATESYTD()’s cumulative behavior.

Recommendation:
Use DATESYTD() for YTD calculations unless you require a custom date range.

References:

DATESYTD function – Microsoft Docs

DATESBETWEEN function – Microsoft Docs

Re: Difference between DATESYTD() and DATESBETWEEN()

v-sshirivolu — Mon, 06 Oct 2025 10:32:25 GMT

Hi Petja ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Re: Difference between DATESYTD() and DATESBETWEEN()

Petja — Mon, 06 Oct 2025 10:50:12 GMT

Hi,

Thanks for your response. Based on my experimenting, the measure becomes undynamic if you try to adjust BOTH, the start and the end date.

The problem I'm trying to solve here is that the data I have is lagged by days, so during the first days of a new year, I'd need the measures to show last years (and the year before that) data for some days.

Re: Difference between DATESYTD() and DATESBETWEEN()

v-sshirivolu — Wed, 08 Oct 2025 09:43:03 GMT

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.

Re: Difference between DATESYTD() and DATESBETWEEN()

Petja — Thu, 09 Oct 2025 07:18:21 GMT

Hi,

Thanks again!

Using the first method you described seems to somewhat work, although using:

LagDays = 365

gives slightly different results for some months than using:

Calculate(DISTINCTCOUNTNOBLANK('Fact'[Key]),SAMEPERIODLASTYEAR(DATESYTD(Calendar[Date])))

EDIT: figured out why some of the newest months had differing numbers (the newest date in data used was before October), still have the problem that January has differing numbers.

Using the latter method you described does not work similarly to DATESYTD as it gives the sum per month and not yearly cumulative per month as DATESYTD.