Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Is Invoice Complete vs. Pending

Hey guys, So I am trying to show if an invoice is complete or pending for all lines it exists if it is either.    This is based off 2 columns. Invoice number and reporting date.   Above is...
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous 

 

Try following

 

Complete Pending =
IF (
    CALCULATE (
        MAX ( 'Complete & Pending'[Reporting Date ] ),
        ALLEXCEPT ( 'Complete & Pending', 'Complete & Pending'[Invoice Num] )
    ) < 'Complete & Pending'[Current Date],
    "Complete",
    "Pending"
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

     

     

    Zubair_MuhammadThanks for your help!. However, when I used your formula, it changed 100% of my invoices to pending. In the image below, i've added a second invoice, this one is "complete" as current date > report date for it. The top invoice lines should read "complete" and the bottom "pending".

    Does this view help? I've added your formula below for continuity. Thank you!

     

    Zubair Test = IF( CALCULATE(MAX('Complete & Pending'[Reporting Date ].[Date]),ALLEXCEPT('Complete & Pending','Complete & Pending'[Invoice Num]))<'Complete & Pending'[Currrent Date], "Complete", "Pending")

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      Why do this in DAX and not in Power Query where it would be rather simple? Any reason?

      On top of that, calculated columns should be created in PQ as a routine. DAX can be used but only as a last resort (for several reasons).

      Best
      Darek
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Anonymouswith the salt! :catlol: 

         

        In this instance I was having difficulty getting what I wanted in PQ so I started down the DAX route. So no valid reason besides my knowledge of the program. I'm still quite new to PBi (obviously)

         

        So then. Given the PQ Below, which is the source for the columns above. How can I get what I am looking for based on the information I provided above? 

         

         

        Thanks!! 

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Anonymous 

       

      Just remove the .[Date] from your formula.

      In my formula i just used (MAX('Complete & Pending'[Reporting Date ])

       

      Zubair Test = IF( CALCULATE(MAX('Complete & Pending'[Reporting Date ].[Date]),ALLEXCEPT('Complete & Pending','Complete & Pending'[Invoice Num]))<'Complete & Pending'[Currrent Date], "Complete", "Pending")
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Zubair_Muhammad 

        Thanks! That worked. Can you explain why that date syntax messed up the formula? When I added that column it auto-populated with that info because it's a date heirarchy.