 # Tableau Calculations: To Date

Written by Damien Arazi
Wednesday 3 February, 2021

### 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