YTD calculated Sales field as of a Date Parameter using the DATEDIFF function

Tableau help link: Creating Year to Date and Month to Date Calculations | Tableau Software

In the previous example, the standard SALES measure was used with a calculated date filter and the measure was the standard SALES field. This example shows how to create a YTD SALES calculated field using the DATEDIFF function, eliminating the need for a calculated filter. Both methods are useful depending on the situation.

Calculation for current YTD sales as of a date parameter

This example does not account for multiple years like the previous one. But it can be adapted for that purpose.

  1. Create a Date Parameter and show it so the viewer can enter a value.

2. Calculating YTD for one year is straightforward. Use the Parameter value with the DATEDIFF function to return the value of SALES only when the difference between the year of the invoice date and the year of the parameter date is zero (minimum date), and the invoice date is less than or equal to the parameter date (maximum date).

Conditional Calculated Sales for Parameter Year

3. Calculating YTD for multiple years gets more complex because you now need to select specific months for each year. This calls for a more complete understanding of how the DATEDIFF function works.
This view, DataSelf DATEDIFF worksheet, shows how the order in which you compare date fields in the formula impacts how the DATEDIFF value is calculated.

Year DateDiff

Month DateDiff

The [Inv Date] is first, then the [Date Parameter]

The [Inv Date] is first, then the [Date Parameter]

The [Parameter Date] is first, then the [Inv Date]

The [Parameter Date] is first, then the [Inv Date]

In the formula syntax, the difference is calculated as the 2nd date field minus the 1st date field. I
n the example, DATEDIFF ('year', [Inv Date], [Date Parameter]), the year of the [Date Parameter] is 2022.
If the Year of the [Inv Date] is 2021, then 2022 - 2021 = 1. The [Date Parameter] year is one greater than the [Inv Date] year. Think of the logic as what needs to be added to the 1st date to get to the 2nd date?

4. If the [Date Parameter] value is 3/31/2022, then what are DATEDIFF ‘month’ values to show Jan - Mar 2021? That depends completely on which order you listed the date fields in the DATEDIFF syntax.

DATEDIFF ('month', [Inv Date], [Date Parameter])

DATEDIFF ('month', [Date Parameter], [Inv Date])

If [Inv Date], [Date Parameter] then positive value for the past

If [Parameter Date], [Inv Date] then negative value for the past

In the example above the DIFFDATE ‘year’ value was first specified to create a minimum limit for the ‘month’ value. That is why the ‘month' value can be selected when >=12, or <= -12 without including records for other years.

MTD calculated Sales field as of a Date Parameter using the DATEDIFF function

For MTD Current Year, limit the DATEDIFF ‘month’ value to zero for the current selected month. This eliminates the need for the DATEDIFF ‘year’ line. The day of the month is limited by the [Inv Date] <= [Date Parameter] .

MTD Current Yr Sales Calculation. DATEDIFF 'year' not required

For MTD Prior Year, the DATEDIFF ‘month’ is set to -12.
The day of the month is controlled by the discrete DAY ([Parameter Date]) condition.

MTD Prior Yr Sales Calculation. Discrete DAY ([Date Parameter]) required

Rolling 3 Month calculated Sales Field

DATEDIFF is a good way to select specific time periods relative to the base time period, since it treats date fields as Continuous months within the context of their years. But sometimes it is easier to select the dates based on Discrete months, so the value is the same regardless of the year, as was done in the MAKEDATE section. You choose…