Microsoft Office Tutorials and References
In Depth Information
FIguRE 7-57 Raw balance account data updated for transactions and not in snapshots.
As you can see, account A001 changes its value on January 1, 12, 20, and 25; account A002
changes on January 1, 14, 21, and 26; and account A003 changes on January 1, 15, 22, and 30.
There is no data at the end of month (January 31), and there is no data for all accounts on a
given date (for example, January 12 has an account balance only for account A001). So neither
LastBalance nor ClosingBalance measures we have seen before can work with this data because
their initial assumptions are not valid anymore. We must create a more complex calculation.
The basic idea is that, for each account, you must get the last nonblank date included in the
selected period. The calculation for a single account can be made by using the CALCULATE
function and by filtering data on the LASTNONBLANK date included in the period between
the first date available and the last date in the period. Notice that the date range considered
begins even outside the period: you might request the balance for February and there might
be no rows in that month, so previous dates also must be considered for the interval. You use
a SUMX function to iterate all the available accounts.
SUMX( ALL( Balances[Account] ),
CALCULATE( SUM( Balances[Balance] ),
LASTNONBLANK( DATESBETWEEN( BalanceDate[Date],
LASTDATE( BalanceDate[Date] ) ),
CALCULATE( COUNT( Balances[Balance] ) ) ) ) )