DAX Formula for - Closing Balance based on last non-blank by non-date column

Question

I'm trying to show the total closing balance by month for the dataset below:

[Tranche]   [Maturity Date] [Balance]

T1  1-Jan-16    1000
T2  2-Jan-16    200
T3  1-Jan-16    3000
T3  3-Jan-16    2900
T1  31-Jan-16   1000
T2  1-Feb-16    200
T3  31-Jan-16   3000
T3  2-Feb-16    2900

I have joined the dataset (table LoanSched) with a dates lookup table (Dates).

Here's the DAX calculated field formula:

=CALCULATE ( 
SUM(LoanSched[Balance]),
FILTER ( Dates, Dates[FullDate] = MAX(LoanSched[Maturity Date]) )
)

However, I get the result below which is incorrect. Since Tranche T2's balance ends on a date earlier than T3, the balance is excluded in the monthly total. The way the dataset works, is that the total balance should included balances that appear on the last day of each month and tranche. I'm missing the tranche condition.

I need to calculate the correct balances (highlighted in yellow) below:

Is 7300 really the correct balance for the grand total? Looks like you're double counting the rolling balance of T2 and T3 ...? — Kyle Hale
You're correct. I made a typo. The grand total should be 4100 not 7300, so it just means I do up to step #2 in your solution. — Christopher Tso

Kyle Hale Kyle Hale · Accepted Answer · 2015-11-04T21:30:19

So what you have here is a form of a semi-additive measure, though I don't quite understand that grand total as it relates to the subtotals - what it says to me is that each "tranche-maturity date" combination is an independent instrument, so it doesn't entirely make sense to use traditional time intelligence - like instead of months that could just be some other arbitrary hierarchy. Is that correct?

Anyway, based on your criteria, what you want is basically

a calculated measure that returns the last non blank balance within a month for a given tranche;
another measure which adds up that measure for each tranche to get a "maturity month balance";
and then a final measure that adds up that measure for each maturing month to get a "total balance".

For #1, this is the traditional formula:

TrancheEndingBalance := CALCULATE (
        SUM ( ClosingBalance[Balance]), 
        LASTNONBLANK (
            Dates[FullDate],
            CALCULATE ( SUM ( ClosingBalance[Balance] ) )
        )
    )

And then #2 is just a SUMX across tranches:

MaturityMonthEndingBalance :=
SUMX ( VALUES ( ClosingBalance[Tranche] ), [TrancheEndingBalance] )

And #3 a SUMX across maturity months:

TotalEndingBalance :=
SUMX ( VALUES ( Dates[MonthYear] ), [MaturityMonthEndingBalance] )

Please note these measures essentially only work for the layout you've described, but it sounds like that's the only way to get at the correct balance for a given set of tranches and maturity dates, so form follows function, as it were.

DAX Formula for - Closing Balance based on last non-blank by non-date column

1 Answers