Display values based on selected date for multiple time frames

[intapiuser]

 Analytical Need 

You want to display several ‘to date’ calculations for multiple time frames (i.e. week to date, month to date, year to date) based on a date selected by dashboard viewer. You want it to be visually appealing and minimize the work for dashboard designers. Additionally, you may have irregular fiscal periods.

 Modeling Challenge

Out of the box dashboard functions (YTD, MTD, etc) will not suffice because:

1. The fiscal period is irregular, i.e. FY2018 starts on Jan 2 and FY2019 starts on Jan 3
2. It requires the designer to explicitly define each calculation for each time period
3. Viewers cannot select a time frame(s) without plugins or customization.

Solution

1. In the elasticube, create a date mapping table – for each reporting date (the date the user will select), include records for each date and time frame to which it relates. For example, let’s say FY2019 begins on Jan 2. For reporting date Jan 10, your resulting date dimension would have:
   
2. Link the Date field to the date field on your fact table(s).
3. The above table is a M2M by default so you MUST do the following to avoid incorrect results and potential performance issues:
   • Add a single select filter for the Reporting Date.
   • Group by Time Frame or add a single select filter for the Time Frame

Below is the pivot table definition. The dashboard designer simply defines the values and groups by time frame. There is no need to explicitly define the value for each time period, greatly reducing design effort. It also displays nicely with the time frame grouping.:

As shown below, the viewer should select a single reporting date. In this example we also allow the viewer to select the time frames we want to see.

That's it!