Point in Time comparisons with future dates

Mon, 25 Jul 2016 06:56:13 GMT

Hi All,

In general, we restrict the Calendar to Maximum date of the fact data.

I have a requirement to calculate YTD, MTD, Prev YTD, MTD dynamically the following way which will have future dates as well:

1) Calendar data is coming from the source DB and it will have dates in future. Eg: If current date is 25-Jul-2016, Calendar can have 31-12-2017 as Max Date.

Future dates are needed for budgeting purpose

2) Fact will always have the history data.

3) The YTD, MTD should be defaulted to the most recent fact data when an App is opened ( i.e. when no selection is made) and Prev YTD and Prev should be offset accordingly.

4) If the user makes any selection ( be it a date of the past or of the future), the values should be calculated for that period till the Maximum of the selected date.

Eg: If the user selects 21-05-2015 and 25-06-2015,

then YTD should be calculated from 1-Jan-2015 to 25-06-2015.

MTD should be from 1-Jun-2015 to 25-06-2015.

5) Similarily, the user makes a selection in future, then the behaviour should be as follows:

If the user selects 21-05-2017,

then YTD will be 0 since there are no measures/facts, Prev YTD should be calculated from 1-01-2016 to 21-05-2016

MTD will be 0, Prev MTD should be calculated from 1-05-2015 to 21-05-2015

Any suggestions to implement this requirement?

-Mahi

Re: Point in Time comparisons with future dates

Gysbert_Wassenaar — Mon, 25 Jul 2016 08:26:10 GMT

Use an As-Of table that includes future dates. See this document for an example of the As-Of table concept: Calculating rolling n-period totals, averages or other aggregations

topic Re: Point in Time comparisons with future dates in App Development

Point in Time comparisons with future dates

Re: Point in Time comparisons with future dates