Tableau : How to Create YTD, QTD and MTD

Image source

Year-to-date (YTD) represents the period from the beginning of the fiscal year to the present date. Management can use YTD details as a quick check-up on the company’s financial health. By measuring YTD performance, a company can assess its performance to date and determine how its current track compares to its prior periods and internal forecasts, as well as for benchmarking purposes.

Here I will explain how to create YTD, QTD and MTD as a filter to use as parameter so that it can be used to calculate all the measure values, as well as YTD, QTD and MTD for the Sales field. I am using SampleSuperStore Data for this example.

Steps to create YTD

1. Create a Calculated Field named YTDSales.

IF YEAR([Order Date])=YEAR(TODAY())
AND
[Order Date]<=TODAY()
THEN [Sales]
END					

This calculates the sales amount from the beginning of the year to the current date. In this calculation, the first part YEAR([Order Date])=YEAR(TODAY()) selects the dates from the current year. The second part of the calculation [Order Date]<=TODAY() selects every date less than or equal to today. TODAY() is the date function that returns the current local system date.

2. Drag YTDSales pill to Text under Marks to display YTD Sales value in the report.

Steps to create QTD

1. Create a Calculated Field named QTDSales.

IF DATEDIFF('quarter',[Order Date],TODAY())=0 
AND
[Order Date]<=TODAY()
THEN [Sales]
END

This calculates the sales amount from the beginning of the quarter to the current date. In this calculation, the first part DATEDIFF('quarter',[Order Date],TODAY())=0 selects the dates from the current quarter. The second part of the calculation [Order Date]<=TODAY() selects every date less than or equal to today.

2. Drag QTDSales pill to Text under Marks to display QTD Sales value in the report.

Steps to create MTD

1. Create a Calculated Field named MTDSales.

IF DATEDIFF('month',[Order Date],TODAY())=0 
AND
[Order Date]<=TODAY()
THEN [Sales]
END

This calculates the sales amount from the beginning of the month to the current date. In this calculation, the first part DATEDIFF('month',[Order Date],TODAY())=0 selects the dates from the current month. The second part of the calculation [Order Date]<=TODAY() selects every date less than or equal to today.

2. Drag MTDSales pill to Text under Marks to display MTD Sales value in the report.

Steps to create YTD as parameter

1. Create a Calculated Field named YTDFilter.

IF YEAR([Order Date])=YEAR(TODAY())
AND
[Order Date]<=TODAY()
THEN TRUE
ELSE
FALSE
END

This parameter returns true for the current year and false for all other years. This helps to calculate all the measure values for the current year with a calculated field. In this calculation, the first part YEAR([Order Date])=YEAR(TODAY()) selects the dates from the current year. The second part of the calculation [Order Date]<=TODAY() selects every date less than or equal to today.

Steps to create QTD as parameter

1. Create a Calculated Field named QTDFilter.

IF DATEDIFF('quarter',[Order Date],TODAY())=0 
AND
[Order Date]<=TODAY()
THEN TRUE
ELSE
FALSE
END

This parameter returns true for the current quarter and false for all other quarters. This helps to calculate all the measure values for the current quarter with a calculated field. In this calculation, the first part DATEDIFF('quarter',[Order Date],TODAY())=0 selects the dates from the current quarter. The second part of the calculation [Order Date]<=TODAY() selects every date less than or equal to today.

Steps to create MTD as parameter

1. Create a Calculated Field named MTDFilter.

IF DATEDIFF('month',[Order Date],TODAY())=0 
AND
[Order Date]<=TODAY()
THEN TRUE
ELSE
FALSE
END

This parameter returns true for the current month and false for all other months. This will help to calculate all the measure values for the current month with one calculated field. In this calculation, the first part DATEDIFF('month',[Order Date],TODAY())=0 selects the dates from the current month. The second part of the calculation [Order Date]<=TODAY() selects every date less than or equal to today.

Many organizations use calendar year as fiscal year, but before setting up YTD, QTD and MTD, we need to check the time period the organization follows, data structure and how often the data gets refreshed. My examples are using TODAY() assuming that data is getting update on a daily basis.