The Data School - Creating and understanding a year to date (YTD) calculation in Tableau

In this blog post I’ll be covering how to create a time-period to date calculation. The example I’ll be using is a year to date sum of profit shown below;

In this post I’ll break down the calculation above using a drill-down style to dive deeper into more complex aspects of the compilation.

Firstly let’s group the lines and break the overall calculation into sections to provide an overview of what’s going on;

1 – we open with a FIXED function. This is important as it fixes the calculation to only look at a certain time frame
2 – we’re looking to calculate the sum of the profit within the time frame specified so use the SUM function
3 – in this section we create conditions for the SUM calculation in part 2 to only sum up dates between the requested period. This is done using an IF and AND statement
4 – This is the second part of the SUM function in part 2 telling the calculator what to sum up (in this case [Profit])
5 – The final part of the sum statement in part 2

Drilling down into the calculation

Now I’ll go into more detail about the more complex parts of the calculation; parts 1 & 3

1- What does the FIXED mean and what is it doing?

The FIXED function is a Level of detail (LOD) function. LOD’s allow us to compute aggregations that are not at the level of detail in the data set.

More simply put, the function allows us to only look at a part of the data that we want to analyse. It acts similar to a filter but the calculation is done before the filter in the visualization and acts independent of filters added in the filter card of Tableau (for more information please look at Tableau’s order of operations).

In our example the FIXED aggregation will be on the selected dates, allowing us to only look at a certain time period which we specify in part 3.

3- The date calculations of the Year-To-Date calculation

Part 3 consists of two lines of code, however there are nested functions within this so I’ll be sure to break it down as much as possible.

Explaining these 2 lines of code in words;

Line 1 – if the order date equals Year(current year, -1 year (today-1 day) ) – *denoting one year and one day ago from today
Line 2 – AND if the order date is larger than or equal to the (one year and one day ago from today) or smaller – meaning if the date is from that date to today.

This type of calculation makes the visualization dynamic and will update the visualization to show data compared to today whenever the data is refreshed.

Understanding the syntax of these 2 lines

For me the most difficult part to understand was these 2 lines and the structure. I’ll break them down further to explain what each typed part of the calculation section means.

Line 1

IF YEAR([Order Date]) = YEAR ( dateadd('year', -1, DATEADD('day', -1, TODAY() ) ) )

This line selects the prior year of the order date for analysis by equaling the year of order date to the year of order date you want. It does this by using the DATEADD function. This function changes a date using conditions. The Tableau explanation is as follows;

The function selects the prior year by using this function comparing it to;
one year before
one day before today

= YEAR ( 
                dateadd('year', -1, DATEADD
                                         ('day', -1, TODAY() ) 
                       )
        )

With the second part of the calculation (one day before today) being the called to nested DATEADD function within the overall function.

Two DATEADD functions are used within each other as there are two comparative dates in the function.

Line 2

AND [Order Date] <= dateadd('year', -1, DATEADD('day', -1, TODAY() ) )

The second line asks if the [Order date] is smaller or equal to the same;
one year before
one day before today

AND [Order Date] <= 
                           dateadd('year',-1,
                                             DATEADD('day',-1, TODAY() )
                                  )

Creating the range of analysis needed (the same format as above)

Components within this can be changed for different periods of analysis. For example if you would like to look at one month to today, you can change the ‘year’ section of the DATEADD to month to see the prior month instead;

In this post I experimented with a different drill-down style instead of a how-to type of blog. If you have any feedback, advice or recommendations on this post or how to improve please comment below or contact me on twitter @vikb03.

Thanks for reading!

The Year to date code used;

{ FIXED :
        SUM(
                    IF YEAR([Order Date]) = YEAR( dateadd('year', -1, DATEADD('day', -1, TODAY() ) ) )

                    AND [Order Date] <= dateadd('year', -1, DATEADD('day',-1, TODAY() ) )

                    THEN [Profit]

                    END
           )
}