The Last functions return a list of the last n given time periods, prior to the given time period. For example, you may create a custom list of the last 6 months. If you select October 2009 from the slicer, a list of all months, from April 2009, up to and including October 2009, will be returned.
The Last functions return:
- Custom calculations, which return a custom member when added to the query. The calculation produced is an aggregated standard list filtered by the Last n elements.
- Custom lists, which return a list of dates when added to the query. The calculation produced is a standard list filtered by the Last n elements.
If you want the option to inject a date into the query at runtime, and see the Last n time periods prior to the given date, be sure to select "Parameterize all Calculations". This will produce a parameter, which is automatically added to the query when you select one of the elements generated by the Time Intelligence wizard. If the Last calculations are not parameterized, then the Last n time periods prior to the most recent date in the model will be returned.
Examples
Adding Calculations
We want to see the last n number of months, so select the month hierarchy, and then select the "Last" calculations:
The elements generated are added to the target hierarchy's Custom Elements panel. Add the required element(s) to the query. Here, a list of the last 12 months is selected. The query returns a list of the last 12 months in hierarchy:
Parameterization
In order to inject the required date at runtime check "Parameterize all Calculations" before creating your calculations:
This will produce a parameter (blue highlight below), along with the 'Last" elements. Now when the "Last 12 List" element is added to the query, a slicer is also added (red arrow). Choose the end date from the slicer; the previous 12 elements will be returned:
Here, "Last 1" is selected, so only the element chosen from the slicer, and 1 previous element, are returned:
Here, a list of the last 3 months is selected, as well as a custom member of the last 6 months:
