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...
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.

     

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.

 

 

 

  • InsightSeeker's avatar
    InsightSeeker
    Helper III
    2 years ago

     

    Hi Anonymous  - My result should be as below.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.