Report and input value with YTD in BPC Embedded Model
Particular for balance sheets reports a period to data calculation is needed for consolidation as well as for planning. Most of the time one has a year to date calculation called YTD which takes beginning of the current year (either the calendar year or fiscal year) and continuing up to the present day. Also QTD (quarter to date) can be found starting with the beginning of the current quarter.
While BPC standard provides both periodic or YTD storage and periodic or YTD display, BPC embedded only can store data periodical since basic InfoProviders in BW don’t store aggregated data. To calculate the YTD values the analytic engine (OLAP) of BW provided a feature for the standard time dimension (info objects: 0CALDAY, 0CALMONTH and 0FISCPER), namely a current member variable. The SAP standard documentation shows some very nice examples how model YTD computations with restricted key figures and restrictions of type interval from start of the year till the current member as upper border. Even more complex use cases can be achieved when spanning over multiple years by using multiple intervals.
For example for year 2012 one can set one hidden key figure
With the first line we can ensure that following years do not get any results. For year 2013 another hidden key figure
Ensures that only this year gets values. We now add all those hidden key figures to one general visible key figure showing YTD for each year.
We plan to simplify this kind of modeling by introducing new variables for the start and end of period.
So far we talked about reporting only. But how about using YTD in planning and make the key figure input ready. As always planning is a kind of inversion of reporting, so changing an input-ready YTD value should change only the last period value contained in YTD. This can be achieved with input-ready formulas as follows.
First we model a hidden key figure restricted to year to date minus one period. When using 0CALMONTH then YTD-1 is the previous month.
Then we have a second hidden key figure to normal perodic value which is input enabled.
Now we create a visible formula which add the YTD-1 value and the current period value to have again YTD
To make this calculated key figure input ready we also set it to be input ready and define an inverse formula to set the current periodic value out of the change of the YTD value.
Then we can start planning on the YTD key figures.
One restriciton on this approach is that in this example I only can update one period value per Material and then a roundtrip to the server is needed. In other words the user needs to press ‘Transfer Data’ before it updates the next period.