The To Date date-time functions return a list of date elements from the same level as the given element, beginning with the first date, up to and including the given date. When generated by the Time Calculation Wizard, a custom member that aggregates this list is also rendered.
The To Date calculations produce:
- A parameter, which is used to inject a date into the query at runtime. Parameters are also added regardless of the separate 'parameterize all' switch.
- Custom calculations, which return a custom member for each calculation.
- Custom lists, which return a list of dates for each calculation.
Depending on the given hierarchy, you may create the following formulations:
- Year to Date: returns a list of dates from the beginning of the given year, up to and including the selected date. For instance, if the given date is May 21, 2019, a list of all dates from January 1 - May 21 is returned. The YTD custom member will aggregate this list; the YTD custom list will return a list of all dates in the custom set.
- Quarter to Date: returns a list of dates from the beginning of the given quarter, up to and including the selected date. For instance, all dates from April 1 - May 21, 2019.
- Month to Date: returns a list of dates from the beginning of the given month, up to and including the selected date. For instance, all dates from May 1 - May 21, 2019.
- Week to Date: returns a list of dates from the beginning of the given week, up to and including the selected date. For instance, all dates from May 19 - May 21, 2019.
Note: Depending on the date-time type, these calculation options may not be available: for example there is no WTD option when looking at a month attribute.
Examples
Adding Calculations
The elements generated are added to the target hierarchy's Custom Elements panel.
From the Time Intelligence wizard, the dateKey hierarchy is selected (red highlight below), and all To Calculations are built:
The dynamic elements generated appear under the Elements panel (red highlight below):
Using Calculations
When the parameter (red highlight below) is added to the query, a single date is chosen from the slicer; the given date is then passed to the query (green highlight). This is effectively the same as a simple date slice filtering the query.
However when the MTD (Month to Date) calculation is added it also retrieves the date parameter - since this will drive the logic for choosing how the MTD calculation will be resolved. If January 28 is selected from the slicer it returns the total sales from the start of the given month (January 1) up to and including January 28 for each product category.
Alternatively, if the MTD list is used and January 28 selected from the slicer, the query returns a list of all dates from January 1 up to and including January 28.
This examples shows QTD.
This demonstrates WTD list.
This shows YTD Lists
Combinations
Because the calculations are all separate, they can, paradoxically, be easily combined in a single query. And because they all share a common parameter, be set in unison.
Here, the user chose May 6 2016. Showing all 4 calculations, they can see Cost metrics by product category for MTD (May 1 to May 6), QTD (April 1 to May 6), WTD (May 1 to May 6) and YTD (January 1 to May 6) in a single query.
