[ Followup guest post by Hans Romeijn: Calculating Period-To-Date/PoP with Indicators for Better Performance ]
Year to Date (YTD) and Year over Year (YoY) calculations are very important in business dashboards. Jim Dehner recently wrote a great post on the topic. It inspired me to present additional approaches for the issues.
Calculating YTD and YoY without LOD
We have been using YTD and YoY long before LOD (level of details) appeared. So we are not dependent on LOD at all.
Here is how we calculate YTD, YTD Sales, Previous YTD, Previous YTD Sales and YoY Change %. Note that YTD and Previous YTD can be applied to any other measure's YTD calculations.
What is good about LOD?
With LOD, such as below, it provides portability when using the same value in different sheets with different dimensions. (Caveat: make your filters contextual when using LOD). If you use it in a single sheet, LOD is not a concern. In most cases, we don't need LOD.
Formatting YoY
When calculating YoY change % using the native quick table calculation "Percent Difference", we find that the first column is empty. Visually it's not an agreeable thing, although we perfectly understand why. With a little formatting, we can make the viewers feel better like below.
Double click the green pill of Percent Difference and use ZN() to wrap around the formula. Then format the pill as follows.
This will put a dash "-" in the empty column. If you wish, we can put a N/A there.
Calculations for QTD/QoQ, MTD/MoM and WTD/WoW
Note that you can replace the date part 'year' in YTD/YoY calculations by 'quarter', 'month' or 'week' in both DATEDIFF() and DATEADD() for calculating QTD/QoQ, MTD/MoM and WTD/WoW. Here are the calculations for QTD, Previous QTD, QoQ Change % and respective sales. You can apply QTD and Previous QTD to calculating other measures.
Calculations for QoPYQ: QTD over Previous Year QTD
The companion workbook can be downloaded here.
Date Grain and Anchor Date as Parameters
PS. Tom T left a comment below saying that we can use a parameter for the date grain like year, quarter, month and week. That's very true. It will allow user to select a date grain. So here is the parameter Date Grain:
And here is the Period to Date calculation, Period being defined by the Date Grain.
Hi - thanks for the mention -
ReplyDeleteWith a little bit of tweaking, you can also make this even a bit more generic by using parameters to select the date part and the measure. Very likely, you could also select a date to compare to
ReplyDeleteTom,
DeleteJust added a section at the bottom of the post. It describes what you suggested. Thanks a bunch!
Hi Alex,
ReplyDeleteI've an issue. When you compare QoQ, both the quarters are in the same year. How do you compare the current quarter with last year's quarter ?
Updated the above post with QTD over Previous Year QTD. Details can be found in the companion workbook.
DeleteThanks
DeleteHi Alex,
ReplyDeleteI need some more assistance as I'm stuck. I wonder if you can come to my rescue. I need to build this formulae, but my fiscal year starts in February. Tableau does not factor fiscal dates in the calculation. I tried following these steps https://tarsolutions.co.uk/blog/fiscal-dates-in-tableau/#comment-3607
However, prior year/quarter calculations do not work. I'll owe you a ton of gratitude if you can enlighten mortals like us with your gifted knowledge. Thanks again.
Could you find me in LinkedIn or Twitter so that we can chat a bit?
DeleteSure. Just sent you an invitation on Linkedin. My real name is Farrukh Kidwai
Delete