Date Data Points

hi Mad Amruthur

Before we get onto the calculation side of things, you should set up your aggregate properties of your model correctly for this type of Measure.

Metrics like Cash Balance, Inventory Level and others need a special aggregation rule. It makes no sense to sum Cash Balance by Time as you always want the latest value for a particular time period. You do however want to sum by say Store or Region or whatever. For this reason these types of Measures are known as semi-additive, i.e. they aggregate down some dimensions but not others, typically time.

Pyramid supports semi-additive Measures and this can be set in your model definition. there are three types of semi-additive aggregations, Cumulative, First Child and Last Child.

• Cumulative - adds each value to the previous one to create a running total down any time oriented hierarchy
• First Child - returns the value of the Measure on the first instance of the lowest level if a regular hierarchy or the first date in the selected time period
• Last Child - returns the value of the Measure on the last instance of the lowest level if a regular hierarchy or the last date in the selected time period

Once set up, you don't need to worry about working out what is the last value for selected time period. If you select a year, it will give you the value on the 31st December, if you select June it will give you the value on June 30th etc.

If you've already set up your model like this, then great, let me know.

If not, I suggest you go read up a bit on semi-additive Measures, adjust your model, then come back here where we'll look at your calculations. See our Help for an example.

Hope that helps.

Ian