Date dimension is one of the most important dimensions in data structure. Most data are transactional ones with a timestamp attached to each transaction record. Needless to say, time series analysis is one of the most important analysis methods.
Let's get into the problem statement based on a simple example: two stores have their sales tallied in two tables (two data sources). The headquarter wants to view the overall sales along time.
Here are the very simplistic reports from the two stores:
The dates in the two stores are non-inclusive sets. Let's see how to use scaffold to blend the two tables together and create a single chart to view the overall sales.
Scaffolding
As we mentioned in Lossless Data Blending via Scaffolding, we need to build a superset of the dates in both tables. For date dimension it is much easier. It is a two-row table with the min date and the max date of combined set of dates in the two tables:
Drag the Date to Columns shelf and turn on "Show Missing Values" in Scaffold's date pill, we will get all the dates in between. This is the KEY in blending date dimension!!
2.Blend Dates
Make sure all date dimensions from the secondary sources are linked with the primary.
3.Use ZN() to aggregate
We then create a measure Total Sales as
- ZN(SUM([Store A].[Sales]))+ZN( SUM([Store B].[Sales]))
- Case [Record]
- WHEN 1 then [Start Date]
- WHEN 2 then [End Date]
- END
Taking Stock with Start and End Dates
Add a comment