MTD, YTD in Tableau for Indian Financial Year
This blog is a sequence to my previous blog https://jyoti05iitd.medium.com/mtd-ytd-in-tableau-using-a-reference-date-parameter-50829230a475
In this blog, I will discuss how to do MTD (MTD_IndianFY), YTD (YTD_IndianFY), Previous Year MTD (MTD_IndianPFY) and Previous Year YTD (YTD_IndianPFY) 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, MTD Sales same period previous year and YTD Sales same period previous year for Indian Financial System starting 1st April 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_IndianFY, YTD_IndianFY, MTD_IndianPFY, YTD_IndianPFY calculations.
Take a range of values with minimum and maximum value. I have selected these values to cover around 3 years of data. You can also keep allowable values to All.
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 a Calculated Field FY Start Date to calculate start date of Financial Year for the selected Date
It is written using the logic that start date of a Financial Year i.e 1st April can be calculated dynamically by taking Year from selected date if Month ≥ 4 or Year -1 from selected date if Month ≤ 3.
e.g If the selected date is 2021–05–31 then FY Start Date is year of selected date i.e 2021+ “-”+“04–01” as Month ≥ 4 or
If the selected date is 2022–01–31 then FY Start Date is year of selected date — 1 i.e 2021+“-”+“04–01” as Month ≤ 3.
Step 4: Write Calculated Field for MTD Sales for Indian FY
The logic for MTD remains the same as it is independent of start date of Financial Year. 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 for Indian FY
Calculated Field for YTD Sales for Indian FY is written using the logic that if
Order Date ≤ SelectedDate and
Order Date ≥ FY Start Date then calculate.
Step 5: Write a Calculated Field PFY Start Date to calculate start date of Previous Financial Year for the selected Date
The logic is similar to FY Start Date calculation. To get start date of previous Financial Year one more year is subtracted in both the cases of Month ≥ 4 and Month ≤ 3.
Step 6: Write Calculated Field for Same Period Previous Year MTD Sales for Indian FY
The logic here is similar to MTD Sales and Selected Date is changed to Corresponding Date in the Previous Year using the DATEADD function.
Step 7: Write Calculated Field for Same Period Previous Year YTD Sales for Indian FY
The logic here is similar to YTD Sales and Selected Date is changed to Corresponding Date in the Previous Year using the DATEADD function.
Also the condition Order Date ≥ PFY Start Date is used.
Final report with Sales On Selected Date, MTD_IndianFY sales and YTD_IndianFY sales, MTD_IndianPFY sales and YTD_IndianPFY sales by Category and Sub-Category is as under:
I have made an attempt to calculate these numbers using a parameter. Please share the blog if you like it.
