MTD, YTD in Tableau using a Reference Date Parameter
In this blog, I will discuss how to do MTD, YTD calculations in Tableau using a Reference Data Parameter.
Tableau version — 2021.2.0
Dataset — Sample — Superstore
Problem Statement — Calculate Sales On the Selected Date, MTD Sales and YTD Sales based on Selected Date Parameter.
The Link to the solution is
The Steps are as follows:
Step 1: Create a Date Parameter (SelectedDate) which will be used to select date. This selected date is a reference to do MTD and YTD calculations.
Take a range of values with minimum and maximum value. I have selected these values to cover around 3 years of data.
Step 2: Write Calculated Field for Sales on Selected Date
It is calculated on the logic that if Order Date is same as SelectedDate then calculate.
Step 3: Write Calculated Field for MTD Sales
Calculated Field for MTD Sales is written using the logic that if
Order Date ≤ SelectedDate and
if month and year of the Order Date is same as month and year respectively of the SelectedDate parameter then calculate
Step 4: Write Calculated Field for YTD Sales
Calculated Field for YTD Sales is written using the logic that if
Order Date ≤ SelectedDate and
if the month and year/financial year of the Order Date is same as month and year/financial year respectively of the SelectedDate parameter then calculate. Here I am assuming that the Financial Year is same as Calendar Year. I will share another blog to do the same calculation for Financial Year starting on 1st April.
Final report with Sales On Selected Date, MTD sales and YTD sales by Category and Sub-Category is as under:
As the SelectedDate parameter is changed, the values for On Selected Date, MTD and YTD sales change.
