Topics: 11 min readtime

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