Looking for DAX to handle Payments Applied to Invoices and Calculate Outstanding Amount

[Karl-D]

Goal: Create DAX Measures to accuratley calculate Outstanding Amt., accurate invoice totals, and potentailly other KPIs.

• All of the columns in the visual below are in a single table in the PowerBI Data Model.
  • There is no separate "Date" table involved currently.
  • There are no separate tables for invoice header and payment detail currently.
    • If no payments have been made, there is just a single row for the invoice with Applied Amt. blank.
    • If the invoice was paid in full with one payment, there will be just a single row for the invoice.
    • If the invoice was paid with multiple payments, there will be multiple rows for each invoice
      (as in the screenshot below)
      
      
    • The only DAX calculation currenlty is "Outstanding Amount".
      Outstanding Amt. = sum('Invoices and Payments'[Inv. Amt.])+ sum('Invoices and Payments'[Applied Amt.])
      
      
    • To make this work properly, the DAX should look for any previously Applied Amt. and Outstanding Amt. should be the remaining unpaid balance. So below has 2 issues:
      
      
      1. Outstanding Amt. is correct for the 06/13 Applied Dt. record,
         but should be 0 after the 06/16 payment is applied.
         
         
      2. The total Outstanding Amt. should be 0 for this Inv No.  
         The total Inv. Amt. here should be $70,387.30 not $140,774.60. 
         
         

Can I do what I'm trying to do here with DAX, or do I need to structure the data differenlty in PowerQuery before attempting to do this type of calculation?

[Karl-D]

So what you originally suggested works overall, but just not at the row level as shown in my last screenshot...

SUMX(SUMMARIZE('Invoices and Payments','Invoices and Payments'[Inv No.],[Inv. Post. Dt.].[Date],"InvoiceAmount",[Invoice Amount],"InvoicePayment",[Applied Amount]),[InvoiceAmount]+[InvoicePayment])

I got past the error thanks to your latest suggestion, but it looks like the filter context is off as it produces large and incorrect values on both rows and total when I use the DAX at the bottom here.

I've tried creating my variable table with both [Inv. Post. Dt.] and [Applied Date].  I think the latter is probably what you meant, as the fomer is the date of the invoice, and the latter is the date that  they payment is applied.

I've also tried joining the variableTable to my main table based upon Invoice Number.

It seems like this is getting sums for lots of stuff in the variableTable, rather than just the invoice I'm looking at?
It seems like ALLSELECTED should know which invoice is in context, especially if I have a relationship defined.
Do I need to use RELATED or something like that?  Sorry, I'm pretty new to this kind of DAX.

Outstanding Amount = SUMX(FILTER(ALLSELECTED(variableTable),[Applied Date]<=MAX([Applied Date])),[InvoiceAmount]+[InvoicePayment])

[Karl-D]

Cool, I see where you're going there with the variableTable, and I got that created successfully.

However, it looks like we're missing something in the final step now?

[BrianConnelly]

Remove the 'Invoices and Payments'...just keep [Inv. Post. Dt.]

[Karl-D]

Thanks Brian.  That got my totals right, but how can I make the yellow number = 0?

In other words, the first payment on 6/13 brought my outstanding amt. down to $47,388.73, so after applying the second payment on 6/16, the outstanding should be 0.

[BrianConnelly]

Try it this way...

Invoice Amount =  SUMX(SUMMARIZE(Table,Table[Invoice],"Invoice Total",MAX(Table[Inv. Amt.])),[Invoice Total])

Applied Amount = SUM(Table[Applied Amount])

variableTable = SUMMARIZE(Table,Table[Invoice],Table[Invoice Date],"InvoiceAmount",[Invoice Amount],"InvoicePayment",[Applied Amount])

OutStanding Amount = SUMX(FILTER(ALLSELECTED(variableTable),Table[Invoice Date]<=MAX(Table[Invoice Date])),[InvoiceAmount]-[InvoicePayment])

[BrianConnelly]

Try These...

Invoice Amount =  SUMX(SUMMARIZE(Table,Table[Invoice],"Invoice Total",MAX(Table[Inv. Amt.])),[Invoice Total])

Applied Amount = SUM(Table[Applied Amount])

OutStanding Amount = SUMX(SUMMARIZE(Table,Table[Invoice],Table[Invoice Date],"InvoiceAmount",[Invoice Amount],"InvoicePayment",[Applied Amount]),[InvoiceAmount]-[InvoicePayment])