As like the other calculations, MTD, YTD and YTG (in case of forecast data) are commonly in need calculations particularly when we have to use time dimensions like Year/Month(Fiscal Periods).
Here I want to discuss about the above mentioned time calculations when we are analyzing the data in story for the current Year. Product offers us the direct functionality or relevant flags for MTD and YTD.
We have direct flags for Current Periods and Previous Periods with sub flags like 1.Full Period 2.To Date. “To Date” is very much useful for the calculations of MTD and YTD. All these options are available only when the object that is created as Date Dimension in the SAC Model.
Lets Consider this example we as i have Forecast Version of data coming from model, this is a combination of Actual and Planning data.My MTD and YTD Calculations would give actual data and where as YTG is obviously forecast information.
Data is displayed as below:
MTD : MonthToDate calculation, want to display Sales measure, we are going to restrict measure based on the Order Date to the Month Granularity by creating a restricted measure, we can restrict “Sales” by the standard system date. Under To Date option, we need to select “Month” granularity for MTD.
Now the values for “MTD Sales” will have data for Aug 2020 only as the current system date is 4th August 2020.
YTD: YearToDate, Measure “Sales” has to be restricted by the till date in the current year from 1st January of the current year. We have the direct options enabled for us based on the date dimension. But here the granularity would be “Year”.
Now the YTD Values are being shown from starting of the current year to current month. Both MTD and YTD show us the data from Actual version of data.
YTG:YearToGo, Measure “Sales” Should be restricted from current Month to till the end of the year. However there is No Direct Flag to achieve the same. We can work around for this calculation with the Calculation Measure rather than Restricted Measure.
In my current scenario, Measure “Sales” has data for the entire Current Year(Jan – Dec) and Measure “YTD Sales” has data Till Current Month ( Jan – Aug), Calculated measure which subtracts YTD from the Full Year can give me YearToGo. Which is forecast value, helps me to analyze the expected Sales for future months.
“YTG Sales”,will now show data for future periods:
Something seems to wrong here as we could see the data for the current period also for “YTG Sales”. It’s not the Case 🙂 As I have data granularity Day for Date Dimension on which these Date Calculations are performed, as the days completed in current month are added to YTD calculation and the remaining days of the current month are considered in the YTG Calculation. Thanks to Product for the absolute Precision. We can consider YearMonth/FiscalYearPeriods objects as date dimensions to consider only data granularity till Month Level.
I have now removed Order Date from the table view to see the consolidated level of data for these Date Calculations
Along with MTD,YTD and YTG, many other date calculations like CurrentYear,YearToQuarter and Previous Periods like last Month,last Quarter and Last Year can be derived on the objects once they are created as Date Dimensions.
Thank You …!!!