on - edited on by
DRay
Analytical Need
It is always a common ask from the business to compare the daily metric of a selected date and then compare it to the last year same period metric. The same period could sometimes be the same day of the same week of last year – for example, if we are looking at the number of orders for 12/23/2019 (which is the Monday of Week 52 of year 2019), we want to compare it against the Last year’s same day of the same week (which is the Monday of Week 52 of 2018 – 12/24/2018).
Modeling Challenge
Usually when we want to slice data by time context, we use the date dimension table but that will not be enough to go back to the last year’s same week number and day number. In this case, we would need to map each date to its equivalent date for this kind of use case. With the normal date dimension table and the available date functions, we cannot do this.
Solution
If you look at the logic to do a date to date mapping on a date to the last year same day of the same week, we must go back by 52 weeks i.e., 364 days (52*7) from any given day to find the equivalent day in the previous year.
STEP I
Note: We are using the date dimension table from this post and we are naming it ‘Dim_Date_Updated’.
Now, create a custom column named Last_Year_Same_Day using the following expression.
createdate(getyear(Date)-1,getmonth(Date),getday(Date))
Ensure the custom column has a data type of Date Time and then, run a ‘Changes Build’ for the column to be built into the model.
Step II
Now that we have two Date fields in the ‘Dim_Date_Updated’ table, we can use them to do simultaneous analysis on the two dates at the same. But to facilitate this, we would need the fact table (transaction table) to be connected to both the date fields by duplicating the fact table. We need to first duplicate the Fact table (‘Fact_Orders’) and name it as ‘Fact_Orders_LY’, then connect Dim_Date_Updated table to Order_Date of Fact_Orders and Order_Date of Fact_Orders_LY using Date and Last_Year_Same_Day respectively. T
The data model with the tables should look like below:
Now, let us execute a Full Build.
Building Widget:
The goal is to build an indicator widget which shows the number of Orders for this year’s date as the Primary Value and that of the last year’s date as the secondary. We would have to use the Fact_Orders_LY only in this widget but the rest of the dashboard can use the Fact_Orders table.
Let us first set up a dashboard filter on the ‘Date’ field of the ‘Dim_Date_Updated’ table and set the context to days, then select ‘Calendar’ and choose a date.
Then, in the indicator widget:
Primary Value: (use the Fact_Orders table)
Sum(Sales)
Secondary Value: (use the Fact_Orders_LY table)
Sum(Sales)
Basically, in the primary value, the total sales for today’s date (from dashboard filter) is aggregated. Whereas in the secondary value, the date that used to join with the Fact_Orders_LY table is the equivalent date from the previous year, so we get the total sales from the last year, same day of the same week num aggregated automatically.
Using this as the base logic, we could implement any use-case that involves a date to date mapping, however it might require the use of another mapping table and LOOKUP function to fetch the equivalent date for every date in the Dim_Date table.
