Microsoft Office Tutorials and References
In Depth Information
If you browse this data at the day level in the PivotTable by using the same measures as the
previous example, you see the results shown in Figure 7-55.
FIguRE 7-55 Browsing data at the day level displays rows with no balance data.
As you can see, the measures defined to display values at the end of the period suffer an
unpleasant side effect: all the dates are visible, even those for which there are no balance
data available. If you want to display just the rows corresponding to dates with balance data
defined, you have to modify the measures, checking the existence of data in the Balances
table, in this way:
ClosingBalanceMonth2
= IF( COUNTROWS( Balances ) > 0,
CLOSINGBALANCEMONTH( SUM( Balances[Balance] ), BalanceDate[Date] ),
BLANK() )
ClosingBalanceQuarter2
= IF( COUNTROWS( Balances ) > 0,
CLOSINGBALANCEQUARTER( SUM( Balances[Balance] ), BalanceDate[Date] ),
BLANK() )
ClosingBalanceYear2
= IF( COUNTROWS( Balances ) > 0,
CLOSINGBALANCEYEAR( SUM( Balances[Balance] ), BalanceDate[Date] ),
BLANK() )
Search JabSto ::




Custom Search