DateDiff is useful for selecting multiple time periods in relation to a specified base date.
If you specify 2023 as the base date, the 2022 has a DateDiff (year) value of -1. It is one year behind.
2021 has a DateDiff value of -2, etc. If you create a filter/selection calculation to select when the DateDiff(Year) value is 0, -5, -10, then the years selected will be:
2023-0 = 2023, 2023 -5 = 2017, 2023-10 = 2013.
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 a previous worksheet view, 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.
This example does not account for multiple years like the previous one. But it can be adapted for that purpose.
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).
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.
The [Inv Date] is first, then the [Date Parameter]
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])
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] .
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.
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…