When dealing with facts and measures in any BI tool, you can set the aggregation method (such as sum, count, average etc). This aggregation always applies on the measure (# of customers, $ of sales, Avg basket amount) etc.
However, there are certain (semi-additive) measures, specially in Finance, which require special handling. Things like Inventory snapshots, or your account balances can be aggregated across other dimensions, but not across time.
i.e. The balance of each day in a month would not be summed to calculate month’s balance. Rather, we would consider the balance value of the last day in the month as the balance of the month.
Those familiar with Oracle BI would know this is easily achieved using the Aggregate by Dimension functionality.
In PowerBI, the same can be achieved using the DAX LASTNONBLANK function:
CalcMeasureName = Calculate(AggregateFunction(MeasureToAggregate),
LASTNONBLANK ( DateDimension, CALCULATE (AggregateFunction (MeasureToAggregate))))
When using this in an example General Ledger analysis, we have used this as
AP Balance Amount = Calculate(Sum('APBalance'[ACTIVITY_GLOBAL1_AMT]),
LASTNONBLANK ('Time'[Date],CALCULATE ( Sum('APBalance'[ACTIVITY_GLOBAL1_AMT]))))
This returns an output as below, where the data is aggregated across supplier types, but shows the latest value (Dec 2015 and May 2016) across period:
Thank you to Adhil Mowlana for putting this together.
If you're interested in knowing more about this, or in understanding how to get PowerBI to work for you, please click below to reach out to us.