Microsoft Office Tutorials and References
In Depth Information
This expression calculates a value for each date in the BalanceDate table. To get the calculation
only for dates that have at least one transaction (for any account), you must make a test similar
to the one you saw already in the previous section for ClosingBalance measures. Finally, you
can define the complete LastBalanceTx measure by using this DAX formula:
LastBalanceTx
= IF( COUNTX( BalanceDate,
CALCULATE( COUNT( Balances[Balance] ),
ALLEXCEPT( Balances, BalanceDate[Date] ) ) ) > 0,
SUMX( ALL( Balances[Account] ),
CALCULATE( SUM( Balances[Balance] ),
LASTNONBLANK( DATESBETWEEN( BalanceDate[Date],
BLANK(),
LASTDATE( BalanceDate[Date] ) ),
CALCULATE( COUNT( Balances[Balance] ) ) ) ) ),
BLANK() )
This formula produces the result shown in Figure 7-58, in which you can see the balance
updated for each account (one for each column) only for days in which at least one new
balance is present in the Balances table.
FIguRE 7-58 Results of LastBalanceTx measure.
Search JabSto ::




Custom Search