DAX model invoice running balance

Question

I have a table with invoices and a table with payments. The Invoice table consists of invoice id, amount, invoice date, expiry date. The payment table consists of invoice id, amount, payment date. The invoice table have an active relationship with the date table on the expiry date column. The payment table have an active relationship with the invoice table on the invoice id columns.

I would like to be able to show the invoice balance on an arbitrary day. Ie if I filter the report or page on a particular date I'd like to see the acual balance on that day per invoice. Anyone know how to acomplish this without creating a new table and programmatically fill it with invoice balance per day entries?

greggyb greggyb · Accepted Answer · 2015-10-02T14:15:30

Here you go:

InvoiceTotalAmount:=
CALCULATE(
    SUM(Invoice[Amount])
    ,ALL(DimDate) // The active relationship between Invoice[ExpiryDate]
                  // and DimDate[Date] would cause this to only be valid
                  // on the expiry date - we don't want that.
)

PaymentTotalToDate:=
CALCULATE(
    CALCULATE( // We'll manipulate the relationship in the inner
               // CALCULATE() before modifying context based on it
        SUM(Payment[Amount])
        ,USERELATIONSHIP(Payment[Date], DimDate[Date])
   )
    ,FILTER( // Now that that we're looking at the right relationship to
             // DimDate, we can alter the date range in context
        ALL(DimDate)
        ,DimDate[Date] <= MAX(DimDate[Date])
            // Here, we take all dates less than the latest date in
            // context in the pivot table - current date if 1 date in
            // context, else last of week, month, quarter, etc....
    )
)

InvoiceBalanceToDate:=[InvoiceTotalAmount] - [PaymentTotalToDate]

If you're not utilizing that active relationship between Invoice[ExpiryDate] and DimDate[Date], I'd mark it as inactive and the relationship between Payment[Date] and DimDate[Date] as the active one. You could then dispense with the CALCULATE() and ALL() in [InvoiceTotalAmount] and the inner CALCULATE() in [PaymentTotalToDate].

My model diagram:

DAX model invoice running balance

2 Answers