Microsoft Office Tutorials and References
In Depth Information
Browsing data using these measures results in a report like the one shown in Figure 7-56.
FIguRE 7-56 Using measures that display only days for which there is balance data.
By default, the PivotTable in Excel does not display empty rows and columns. For this reason,
the days containing no balance date are not shown: all the measures used in the PivotTable
return BLANK for those days, removing them from the report.
Updating Balances by Using Transactions
The balance account model you saw in the previous section makes an important assumption:
for a given date, either data is not present at all or all the accounts have a balance value for
that date. In case an account does not have a balance value for a date that other accounts are
measured, that account is considered to have a zero balance for that date. This assumption is
good for certain data structures, which are generated by a system that makes a snapshot of
the situation (all balance accounts values) on a given date.
However, some scenarios have a different data model in which the previous assumption
is not valid. For example, consider this other way to collect data about balance accounts.
In the Balances table shown in Figure 7-57, data has been normalized by means of an
Accounts table, which can be seen on the right side of the same figure. (The model used
in this section is available in the CH07-13-ClosingTransaction.xlsx workbook included on
the companion DVD.) Moreover, you can find a balance row for an account only for dates
when a transaction made some changes in the account balance.