Adding calculations to your Planning Analytics model, like a YTD measure, provides users with the values they need without having to export data and do the math themselves. Determining what calculations will be most useful in your model is best decided after receiving feedback from users. Why? Because conversations about what data they’re extracting and manipulating themselves leads to a more comprehensive model. Calculating measures in the format your users need will save them time in having to add values up themselves, and inevitably making a mistake every now and again. A YTD measure is an often-used calculation, so let’s review how to create one.
How to create a YTD calculation
Time needed: 10 minutes
Here we explain how to create a YTD calculation in Planning Analytics (TM1)
- Choose the simple measure for which to create a YTD version.
For example, you could use an Amount measure. Keep in mind, you can create a YTD version of any type of measure that aggregates in totals, like Count or Hours. Measures that calculate using division, like a percentage, will not calculate correctly using this method. For a YTD percentage, the components (numerator and denominator) will need calculated as YTD measures. Then the YTD Percentage measure can be calculated from those components.
- Add a simple YTD measure to your measures dimension.
In our example, we use YTD Amount. Below, you can see the created YTD measure; but note that it does not yet have any values.
- Add attributes to the Time dimension to indicate the previous and next period, for each period you wish to reference to calculate YTD.
The attribute referencing the previous period will be used for the rule, while the attribute referencing the next period will be used for the feeder. The first period in a year will not have a previousperiodYTD value, as that is where we want the YTD calculation to start, and not pull forward the value from the last period of the prior year.
- Create the rule for the YTD measure.
The YTD measure for a period will need to reference the YTD measure from the previous period, and the regular measure for the same period.
[‘YTD Amount’] = N: [‘Amount’] + DB(‘Example’,!Expense Accounts,ATTRS(‘Time’, !Time, ‘previousperiodYTD’),!Version,’YTD Amount’);
The Time dimension in the rule above references the previous period attribute to grab the previous period’s YTD value to build onto.
Below you can see the YTD amount for 2021/Feb is a total of $10, coming from the $5 in 2021/Feb of the Amount measure and the $5 in 2021/Jan of the YTD Amount measure. - Create the feeders for the YTD measure.
The values referenced in the rule need to feed the rule target.
In other words, the two values highlighted in yellow above need to feed the value highlighted in green.
[‘Amount’] => [‘YTD Amount’];
[‘YTD Amount’] => DB(‘Example’,!Expense Accounts,ATTRS(‘Time’, !Time, ‘nextperiodYTD’),!Version,’YTD Amount’);
The Time dimension in the feeder for the YTD Amount should reference the attribute for the next period. But we aren’t quite finished yet. Since the value is YTD, the 2021 consolidation should be set to December’s value, not the aggregation of the entire year. - Create a rule for the year consolidation.
The Time dimension in the consolidation rule should reference the last period of the year. In this example, we extract the year from the period and concatenate 12 onto it. An alternative method would be to have an attribute on the Time dimension that indicates the last period for each year in the dimension and reference that attribute.
[‘YTD Amount’] = C:
IF(ELLEV(‘Time’, !Time) = 1,
DB(‘Example’,!Expense Accounts,SUBST(!Time, 1, 4) | ’12’,!Version,’YTD Amount’),
STET);
You’ve created your YTD calculation!
We’ve now created a YTD calculation that calculates the running total of a measure and set it to consolidate properly. As we said before, you can apply these concepts to any measures requiring a running total. Week-to-date, month-to-date, and quarter-to-date measures all use the same concepts. Create and populate the appropriate attributes for the time dimension being used. The same previous period rule and next period feeder concepts can then be applied to calculate any to-date values. But don’t forget the first step is always having those important conversations with your users so that you’re developing the measures they need!
Keep Reading: TM1 Best Practices: The Power Of Using Global Parameter Flags >>
