Tableau — Year to Date
***
Have not been actively using Tableau since 2016/2017 era, and a request came about to assist with creating a Year to date (YTD) variable. Ok, it’s not Yearn to Date haha, and I will keep this post straight to the point with no fancy storytelling or write-ups. 👻
***
For this exercise, I will be using a random self-created dataset consisting of 4 data attributes:
ReportDate — in date format, from 1st Jan 2018 till 28th June 2022
Region — string, contains two region ‘Central’,’Southern’
SalesRM — Number (random numbers)
CostRM — Number (random numbers)
***
For a start, we can first create a calculated measure to identify the relevant dates:
[ReportDate] <= TODAY() AND DATETRUNC(“year”,[ReportDate]) = DATETRUNC(“year”,TODAY())
And if we were to aggregate the variable of interest, SalesRM, we can leverage on IF ELSE statement to create a aggregated calculated field:
SUM(IF [ReportDate] <= TODAY() AND DATETRUNC(“year”,[ReportDate]) = DATETRUNC(“year”,TODAY()) THEN [SalesRM] ELSE NULL END)
***
But what if we have more than a single measure or metric to perform YTD tracking? Should we then duplicate the same formula to be applied across the other measures in the form of multiple calculated measures? And what if we want to track for the same period last year? Or what if our financial reporting year starts from July onward? How about YTD tracking for different dates, like August 8th, September 19, and not just YTD up till today’s date or this month’s month end date? And not to forget the standard Month to Date (MTD) and a series of other reporting period?
***
Rahul Prasad has a well-written post on dynamic date calculations using sets and parameters in tableau, with the computations performed based on month end’s date, i.e. 31st March, 30th April etc.
Therefore, taking references from Rahul, this post serves as an extension/ modifications to include for different fiscal year start date (taking July 1st as the start instead of January 1st), and up to specific day (instead of month end) for the computations.
As this is a mere extension of the post, no files are attached with this write-up. But do ping me should you encounter any difficulties following the post or if you require the typed- out formula/ tableau workbook/ data file.
***
Before we get started, here’s some tableau functions that would comes handy. Do check up the explanations and writeups on tableau help references.
DATETRUNC()
MAKEDATE()
DATEDIFF()
DATEADD()
***
Step 1: Create Two Parameters
Create the first parameter Param_SelectEndDate with the followings conditions:
“when workbook Opens” is checked
Data type = Date
Allowable values = List
Create the second parameter Param_ComparisonPeriodDate with the following condition
Fixed is checked
Data type=Integer
Then input the list of values and corresponding expected display value
1: YTD with Prior Year
2: Rolling 12 Months with Prior Year
3: MTD with Prior Month
4: MTD with Prior Year
5: YTD FY July-Jun with Prior Year
***
Step 2: Create the calculated variable _ComparisonPeriodDeterminantDate
Notice how the formula has been modified versus Rahul’s example for this purpose? We also skip the creation of “Last Day of End Month Parameter”, and added 5: YTD FY July-Jun with Prior Year as an additional comparison option. [To view, click the image to enlarge.]
***
Step 3: Convert the calculated variable into a dimension and create the two sets from it:
Current_EndDate: selecting only 0
Prior_EndDate: selecting only -1
***
Step 4: Finally, create the variables of interest
Calculated Field Name: _SalesRM_CurrentPeriod_EndDate
Formula: SUM (IF [Current_EndDate] THEN [SalesRM] END)
Calculated Field Name: _SalesRM_PreviousPeriod_EndDate
Formula: SUM (IF [Prior_EndDate] THEN [SalesRM] END)
Notice how the same formula has been simplified versus inserting the entire string of formula at the start of the post?
SUM(IF [ReportDate] <= TODAY() AND DATETRUNC(“year”,[ReportDate]) = DATETRUNC(“year”,TODAY()) THEN [SalesRM] ELSE NULL END)
***
What if there is an ask to report Week to Date (WTD) numbers? For this we can leverage on the start_of_week parameter in the DATETRUNC() function. DATETRUNC(date_part, date, [start_of_week])
Give this a try and have fun! 👻👻👻
***
References:
1. https://resources.useready.com/blog/dynamic-date-calculations-using-sets-in-tableau/
2. https://help.tableau.com/current/pro/desktop/en-us/functions_functions_date.htm
***
