Invoice / Payment Status Using DAX or Measure

[InsightSeeker]

Invoice Payment Status Using DAX or Measure

3 hours ago

I want to get the status of each invoice, indicating whether it is paid, partially paid, or unpaid.

- If the invoice total is equal to the paid amount, the invoice should be marked as Paid.

- If the invoice total is greater than the paid amount, the invoice should be marked as Partially Paid.

- If the invoice total is not fully or partially paid, the invoice should be marked as Unpaid.

Can this be done using a DAX measure?

For PBIX File click here

Result

Inv_Date	Inv_No	Document	Sale_Amount	Inv_Amount	Paid_Amount	Invoice_Status
1-Jul-23	867558757	565787	1200.00	2,510	2,510	Paid
20-Jul-23	867558757	7788686	1310.00	-	-	Paid
3-Jul-23	778807	8768686	3210.00	6,770	3,210	Partially Paid
9-Jul-23	778807	7657657	3560.00	-	-	Partially Paid
16-Jul-23	779049	5699879	3910.00	3,910	-	Unpaid
17-Jul-23	779170	867868	4260.00	4,260	4,260	Paid

[Anonymous]

Hi @InsightSeeker ,

The following DAX might work for you:

Measure = 
  VAR _Inv = SELECTEDVALUE(Sale_1[Inv_Amount])
  VAR _Paid = SELECTEDVALUE(Sale_1[Paid_Amount])
  RETURN
  IF(_Paid < _Inv , 
     IF(_Paid = 0 , "Unpaid" , "Partially Paid"),
     "Paid")

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[InsightSeeker]

Hi @Anonymous - This measure returns the incorrect status for the Invoice 778807. This invoice is partially paid whereas for one sale it is showing paid.

[InsightSeeker]

Hi @Anonymous  - My result should be as below.

[Anonymous · Accepted solution]

Hi @InsightSeeker ,

Thank you for pointing out my problem!

The following DAX might work for you:

Measure = 
  VAR _Inv = SELECTEDVALUE(Sale_1[Inv_Amount])
  VAR _Paid = CALCULATE(MAX(Sale_1[Paid_Amount]),ALLEXCEPT(Sale_1,Sale_1[Inv_No]))
  VAR _Sale = CALCULATE(SUM(Sale_1[Sale_Amount]),ALLEXCEPT(Sale_1,Sale_1[Inv_No]))
  RETURN
    IF(_Paid < _Sale , IF(_Paid = 0 , "Unpaid" , "Partially Paid"),"Paid")

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.