This blog serves as a continuation of my previous content, which detailed the construction of Dynamic KPIs in Tableau through 25 Date Calculations. If you haven't explored that material yet, I suggest starting there for context.
In concluding the aforementioned blog entry, I referenced an insightful article by Ken Flerlage, in which he outlines five reasons for using a calendar or date dimension table in Tableau.
This brief post aims to employ a dimensional calendar table to generate the 25 Date Calculations, all aligned with a Fiscal Calendar tailored to suit the needs of any company. We start by building, a Dimensional Calendar in excel with the essential fields:
Calendar Date
Financial Year
Month Number
Week Number
Note: In this case, financial year starts the first Monday of April and our month is a period number once every 4 weeks (5 weeks every 3 months). Download the Dimensional Calendar Table excel file from here.
Next, we would need to do a join on the date fields of the new Dimensional Calendar Table to the existing Orders Data. This particular example uses the join calculation for the New Order Date to bring all the dates from existing data file onto the current year.
Now that our data preparation is complete, let's move on to the exciting phase. Luckily, we won't have to make extensive changes to the formulas in our set of 25 calculations for constructing our KPIs. The only adjustments required are for the year-to-date (YTD) calculations, as the beginning of the year varies according to the Financial Calendar. Therefore, we'll need to align it with the financial calendar using a Level of Detail (LoD) Calculation.
This Financial Year
//New calculation for fixing today's year
{ FIXED :MAX(IF [Cal Date]=TODAY() THEN
[Financial Year]
END)}13) Order Date Keep YTD
//
IF [Financial Year]=[This Financial Year] AND
[New Order Date]<TODAY() THEN
TRUE END14) Order Date Keep YTD (LY)
IF [Financial Year]=[This Financial Year]-1 AND
[New Order Date]<DATEADD('year',-1,TODAY())
THEN TRUE ENDAfter making these adjustments in the calculations, we can see that our year commences on the first Monday of the month of April, as per our Financial Calendar.
Finally, replace the week numbers in the existing tables with the Week Number field obtained from the joined Calendar Table.
And there you have it! we've seamlessly integrated the financial calendar into our process for updating KPIs, and tables, all structured around our financial years. Here's the Tableau Public link for the Dashboard for 25 KPI calculations with financial calendars attached.
Signing Off,
Yash Sakhuja
Comments