Difference between DATESYTD() and DATESBETWEEN()

[Petja]

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?

[v-sshirivolu]

Hi @Petja ,

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

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

Key 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 Explained:

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

[Petja]

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.

[v-sshirivolu]

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.

[johnt75]

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