YTD and MTD calculations are commonly used to calculate trends and to track performance over a specific period. By comparing month to date with the same period last year, seasonal trends can be identified. Alternatively, the comparison can show the effectiveness of a marketing campaign.
How do we calculate YTD? For this example, we will be looking at sample superstore data. We need can do YTD date by completing the following calculation:
If YEAR([Order Date]) = YEAR(TODAY())
AND
[Order date]<=TODAY() then [Sales]
End.
The first line of the logical statement ‘YEAR([Order Date]) = YEAR(TODAY())’ ensures that that the year of the order date in the same as the current year. The third line: ‘[Order date]<= Today()’ sets the condition that the order date must be before or on the date of today. If both conditions are satisfied, then the calculation will return the sale. This calculation can also be completed with other fields such as profit or quantity.
If we wanted to create a month to date function, we would use the month function instead of the year function in the calculation above.
We can also create a parameter so that the user can pick the date they want to compare to. We create a date parameter as shown below:
We can choose to set the parameter within a certain range or keep the allowable values as all. Then instead of the today function we use our parameter in the calculation so that we have:
If YEAR([Order Date]) = YEAR([Date Selected])
AND
[Order date]<=[Date Selected] Then [Sales]
End.
Now that we know how to calculate YTD, how do we calculate prior YTD? We want to look at data from the previous year and there for need the following calculation:
If YEAR([Order Date]) = YEAR(TODAY())-1
AND
[Order date]<=DATEADD(‘year’,-1, TODAY()) then [Sales]
End.
By applying ‘YEAR(TODAY())-1’ we get the year of the current date -1 i.e. the previous year. We then use the DATEADD function in the third line of the calculation to subtract one year from the current year. By doing so we can ensure that the order date occurs on or before the previous year.
Once gain we can obtain the previous month by using the month function instead of year. We can also use our parameter instead of the today function so that the user can select their own date for comparison.
Another type of date calculation is Year on Year calculation. The YoY calculation allows us to identify growth trends within a metric such as sales or profits over the last year. YoY calculations can allow businesses to evaluate performances and neutralize season effects such as the Christmas period.
We can create a YoY calculation by adding our year of order date on the column and a measure of our choice onto rows. In this example I will be using sales. After adding our metric to columns we click on the carrot next to sales and hover over quick table calculations. We will then see a list of calculations including ‘year over year growth’.
By selecting on this we automatically create the calculation. We can then click on the sum(sales) carrot to edit the table calculation. This will open the following pane:
We can edit our calculation so that it is relative to the first, last or selected year.
We can also calculate Month over Month by using month([Order Date]) instead of Year([Order Date]).