Semi-Additive Measures
A semi-additive measure is a measure that is aggregated across most but not all dimensions. For example, customers’ bank balances cannot be added across time, because a bank balance is a snapshot in time. To create such measures, you can use the %LAST function, an InterSystems extension to MDX.
Consider the following measures:
-
Balance is based on the source property CurrentBalance and is aggregated by summing.
You would avoid aggregating this measure over time, because it would give incorrect results; that is, you should use this measure only in pivot tables that include a time level for rows or columns.
-
Transactions is based on the source property TxCount and is aggregated by summing.
You can define a calculated measure called LastBalance and use the following value_expression:
%LAST(Date.Day.Members,Measures.Balance)
The %LAST function returns the last non-missing value for a measure evaluated for each member of the given set. In this case, it finds the last day that has a value and returns that value.
