topic Re: Looking for DAX to handle Payments Applied to Invoices and Calculate Outstanding Amount in DAX Commands and Tips

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

Karl-D — Mon, 20 Mar 2023 17:05:01 GMT

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?

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

BrianConnelly — Mon, 20 Mar 2023 17:14:39 GMT

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])

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

Karl-D — Mon, 20 Mar 2023 18:05:40 GMT

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.

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

BrianConnelly — Mon, 20 Mar 2023 18:45:33 GMT

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])

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

Karl-D — Mon, 20 Mar 2023 19:12:45 GMT

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?

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

BrianConnelly — Mon, 20 Mar 2023 19:21:00 GMT

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

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

Karl-D — Mon, 20 Mar 2023 21:49:50 GMT

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])