PowerPivot: Last reported balance by date and two-level category

DAX DAX Calculations
1

Hi all – I’d be grateful for any support in troubleshooting my DAX measure, in Power Pivot. I’ve attached a minimum reproduction in Excel:
DAX latest balance test.xlsx (273.8 KB)

I’m trying to maintain a “latest reported balance” over time (row labels: Year/Month/Date), across a range of banking Institutes and Accounts (column labels).

I’m displaying the result of the measure in a pivot table:

  • Each cell should show the balance reported on that Date for the Institute and Account, or if there is no corresponding record, the balance for the last reported date
  • Column totals should show the latest total in that Institute & Account
  • Row totals should show the sum of balances on that Date (or for Months and Years, the sum as at the last Date in that period).

The main issue is that, for cells where there is no original record with the corresponding Date, Institute and Account, the result is blank (instead of returning the value from previous date, as desired).

I’ve tried a couple of techniques, adapted from patterns on this forum and others, with the same outcome:

Cash balance 1:=VAR curr_date = MAX( dimCalendar[Date] )
VAR T1 = ADDCOLUMNS(
	SUMMARIZE( pqrTransactions, pqrTransactions[Institute], pqrTransactions[Account] ),
	"latest_balance", CALCULATE(
		LASTNONBLANK( pqrTransactions[Balance], pqrTransactions[Balance] ),
		FILTER( ALL( dimCalendar[Date] ), dimCalendar[Date] = CALCULATE( MAX( pqrTransactions[Date] ), dimCalendar[Date] <= curr_date ) )
	)
)
RETURN
	SUMX( T1, [latest_balance] )

Cash balance 2:=VAR curr_date = MAX( dimCalendar[Date] )
RETURN
	SUMX( 
		SUMMARIZE( pqrTransactions, pqrTransactions[Institute], pqrTransactions[Account] ),
		VAR last_date = CALCULATE( MAX( pqrTransactions[Date] ), dimCalendar[Date] <= curr_date )
		VAR last_balance = CALCULATE( LASTNONBLANK( pqrTransactions[Balance], pqrTransactions[Balance] ), dimCalendar[Date] = last_date )
		RETURN last_balance
	)

No doubt there’s some fundamental context or inheritance concept that I’m not grasping! Thanks in advance for any help.

2

Answer:

I believe I’ve resolved my issue as follows, by wrapping the SUMX in a further CALCULATE, using that to remove filters on the Calendar table:

Cash balance:=VAR curr_date = MAX( dimCalendar[Date] )
RETURN
	CALCULATE(
		SUMX( 
			SUMMARIZE( pqrTransactions, pqrTransactions[Institute], pqrTransactions[Account] ),
			VAR last_date = CALCULATE( MAX( pqrTransactions[Date] ), dimCalendar[Date] <= curr_date )
			VAR last_balance = CALCULATE( LASTNONBLANK( pqrTransactions[Balance], pqrTransactions[Balance] ), FILTER( ALL( dimCalendar[Date] ), dimCalendar[Date] = last_date ) )
			RETURN last_balance
		),
		ALL( dimCalendar )
	)

I’d still welcome any comments to help my understanding of how & why this works, and my previous attempts don’t. I’d assumed that the inner CALCULATE filters would suffice, but perhaps they are ignored or overridden if an outer filter context is not set explicitly?