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.