Calculating YTD Profit is a great way to simultaneously elevate your analysis and add in some interactivity for your stakeholder. Year-to-date profit is simply the total profit a company has made from the beginning of the current calendar year (i.e 1st of Jan) until a specified date within the same calendar year. I will explain how we can use date & logical functions to create this measure in Tableau, and combine it with a date selector so your stakeholder can easily see the YTD Profit for a date of their choice.
Firstly, we have to create a date parameter that allows a user to select a date and change the YTD profit based on the chosen date.
Clicking the selected dropdown, and the "Create Parameter..." option takes us to the next screen (below).
We then name our parameter accordingly, select what data type we want (in this case we want a date), what the current value is when we open the workbook, and what values we will allow (in this case we will allow all). Which gives us the parameter shown below, and by clicking the box, we can choose the date desired.
Then selecting the same dropdown as the first step, but this time choosing "Create Calculated Field..." we can combine our logical and date functions, along with referencing our date parameter to return the YTD profit for the desired date.
IF Function
The 'IF' function tests a series (the 'AND' function means both expressions must be true) of expressions and returns a specified value if true. In this case, we are returning the Profit field if our statements are true.
YEAR and DATEPART Functions
The 'YEAR' function returns the year of a given date, in this case from our Date Selection parameter, and Order Date fields. The DATEPART function returns the specified date part from the specified date, here we are returning 'dayofyear' which is the day of the year numbered (e.g 1st of Jan is 1, 1st of Feb is 32).
So what are we actually testing?
Essentially, the functions are combining to return the profit up until the specified date. Our statement is asking to return profit only if the year we have selected is equal to the Order Dates (i.e 2023 = 2023) and if the specified day ('dayofyear') selected, is less than or equal to the Order Dates (this ensures profit is only returned for the days before our specified date, and that date itself).
This is just one of many ways to elevate our analysis in Tableau. YTD Profit is a great way measure for analysing how a company has performed up until a specified date, and making a field for this can allow you to subsequently create a "Previous YTD Profit" which can take you a step further.
More to come on some of these measures!