Microsoft Office Tutorials and References
In Depth Information
The Balances table contains a balance for each account and each last day of the month, but
for December the last day available is December 15. If the BalanceDate table contains all the
days for year 2010, including 31 days for December, the LastBalance measure tries to filter
balance data for December 31, which is not available, resulting in a PivotTable like the one
shown in Figure 7-52, where the row for December is missing.
FIguRE 7-52 December and 4th Quarter totals are missing.
A possible solution is to delete rows from the BalanceDate from December 16 through
December 31. In this way, the LastBalance measure returns values as previously shown
in Figure 7-50. Another option is to use the LASTNONBLANK function, which returns the
last date for which a particular expression is not blank. The use of this function is not
very intuitive when the Dates column and the expression you want to evaluate manage
different tables. First of all, this is a formula for a LastBalanceNonBlank measure that
works also with the BalanceDate complete with all the dates through December 31.
LastBalancaNonBlank = CALCULATE( SUM( Balances[Balance] ),
COUNTROWS( RELATEDTABLE(Balances) ) ) )
The preceding formula produces exactly the result you saw in Figure 7-50, without your
needing to remove rows from the BalanceDate table.