Forum Discussion

InsightSeeker's avatar
InsightSeeker
Helper III
2 years ago
Solved

Invoice / Payment Status Using DAX or Measure

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_DateInv_NoDocumentSale_AmountInv_AmountPaid_AmountInvoice_Status
1-Jul-238675587575657871200.00             2,510                2,510 Paid 
20-Jul-2386755875777886861310.00                    -                         -   Paid 
3-Jul-2377880787686863210.00             6,770                3,210 Partially Paid 
9-Jul-2377880776576573560.00                    -                         -   Partially Paid 
16-Jul-2377904956998793910.00             3,910                       -   Unpaid 
17-Jul-237791708678684260.00             4,260                4,260 Paid 
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      InsightSeeker
      Helper III
      2 years ago

      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.