/avatars_200px_circle_Damien_Arazi.png){kind=avatar}
Date Calculation: <DatePart> To Date
Dates seems to be a relatively easy topic, right? And also one of the most common dimensions used for an analysis. While using your date field in your visualisation a simple lookup can help you get previous values, to calculate previous values via calculated field can be a bit more advanced.
There are many different approaches to date calculations. One that can be tricky to create is the To Date comparison.
This is where date calculation becomes complex. We want to present a solid approach, an overview of Year and Month to Date calculations
Creating Today and Today Last Year
First you want to create your Today calculation:
Today()
Today last year calculation:
Makedate(
Year([Today])-1,
Month([Today]),
Day([Today])
)
This will allow us to have 2 new date fields, one for the current year and one for last year. We will use those two dates for our calculations.
Creating the period calculation for our measure
Next we want to create our Year to Date Sales.
sum(
if year([Order Date]) = year([Today]) and
month([Order Date]) < month([Today]) then [Sales]
elseif year([Order Date]) = year([Today]) and
month([Order Date]) = month([Today]) and
day([Order Date]) <= day([Today]) then [Sales]
else null
end
)
The second logical statement will limit the time period to today.
Same goes for our Previous Year To Date Sales
sum(
if year([Order Date]) = year([Today last year]) and
month([Order Date]) < month([Today last year]) then [Sales]
elseif year([Order Date]) = year([Today last year]) and
month([Order Date]) = month([Today last year]) and
day([Order Date]) <= day([Today last year]) then [Sales]
else null
end
)
Quarter To Date Calculation
Quarter To Date Sales
sum(
if year([Order Date]) = year([Today last year]) and
quarter([Order Date]) < quarter([Today last year]) then [Sales]
elseif year([Order Date]) = year([Today last year]) and
quarter([Order Date]) = quarter([Today last year]) and
month([Order Date]) = month([Today last year]) and
day([Order Date]) <= day([Today last year]) then [Sales]
else null
end
)
Current Quarter To Date Sales
sum(
if year([Order Date]) = year([Today]) and
quarter([Order Date]) = quarter([Today]) and
month([Order Date]) = month([Today]) and
day([Order Date]) <= day([Today]) then [Sales]
else null
end
)
Previous Quarter To Date Sales
sum(
if year([Order Date]) = year([Today]) and
quarter([Order Date]) < quarter([Today]) then [Sales]
elseif year([Order Date]) = year([Today]) and
quarter([Order Date]) = quarter([Today]) and
month([Order Date]) = month([Today]) and
day([Order Date]) <= day([Today]) then [Sales]
else null
end
)
Previous Year Quarter To Date Sales
sum(
if datetrunc('quarter', [Order Date]) = datetrunc('quarter', dateadd('year',-1,datetrunc('quarter',[Today])))
and [Order Date] <= dateadd('year',-1,[Today]) then [Sales]
end
)
These calculations will return your values up until the same day count. Very important for a To date calculation.
Month To Date Calculation
When working with month we are facing a couple of additional challenges. We have three different period comparisons we want to have: Month To Date, Previous Month To Date and Previous Year Month To Date. How do we handle January, since it will be compared to December the prior year? The logic we have created for our year will need to be adjusted to this specific case.
Let’s apply this to our sales: Month To Date Sales
sum(
if year([Order Date]) = year([Today]) and
month([Order Date]) = month([Today]) and
day([Order Date]) <= day([Today]) then [Sales]
else null
end
)
Previous Month To Date
sum(
if month([Today]) = 1 then
if month([Order Date]) = 12 and
year([Order Date]) = year([Today last year]) and
day([Order Date]) <= day([Today last year]) then [Sales]
else null
end
else
if year([Order Date]) = year([Today]) and
month([Order Date]) = month([Today]) - 1 and
day([Order Date]) <= day([Today]) then [Sales]
else null
end
end
)
The first statement will solve the challenge for January and compare the Sales against December.
Same goes for Previous Year Month To Date Sales
sum(
if year([Order Date]) = year([Today last year]) and
month([Order Date]) = month([Today]) and
day([Order Date]) <= day([Today]) then [Sales]
else null
end
)
You can now go ahead and create further delta calculations without applying any date filter.
In the embedded workbook you will find some use case, how each fragmentation will contribute to an
overall comparison view To Date.
Few Notes
This approach is especially useful if you don’t use your date field in the visualisation or don’t want (or can) use any date filter. It also will allow you to easily calculate the absolute or percentage delta without the need of using any filters or your date field.
A few tips when working with dates:
-
Build a crosstab to see if your calculations are correct. This will be very useful to troubleshoot which part of your calculation is causing problems
-
If your data contains dates that are only in the past you can use {max(<YourDate>)} instead of Today(), this LOD will give you the latest date.
-
We have included our aggregation directly into the calculations by adding sum in front of the calculation. Just remove it and you will be able to select the type of aggregation.
-
Optionally you could create a reference date parameter instead of using your data to discate the year. Be aware to update your parameter value when the time comes!
-
If you encounter performance problems, using a simple boolean date filter can improve the loading speed while still keeping your data dynamic. For example for a current and previous year filter as a data source or extract filter:
year(<YourDate>)}=year(today())
or
year(<YourDate>)}=year(today())-1
- Damien
Empower your organization with intuitive analytics
Tableau is designed to put the user first because data analysis should be about asking questions and not about learning software. With built-in visual best practices, Tableau enables limitless visual data exploration without interrupting the flow of analysis.
As the market-leading choice for modern business intelligence, the Tableau platform is known for taking any kind of data from almost any system and turning it into actionable insights with speed and ease. It’s as simple as dragging and dropping.
We are a full-stack provider and integrator, relying on extensive experience and best practices to find your unique optimal set-up allowing you to tell the data stories you are eager to tell.
