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
