Forum Discussion
Is Invoice Complete vs. Pending
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")
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
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!!
- I'll show you in a about an hour... when I'm back home :)
Best
D Sorry for the late reply.
Here's the file... Check whether or not it is what you wanted.
Best
D.
Hey Anonymous,
I'm sorry, hopefully I'm missing something. But your file does not solve my problem. You do some cool transformations in it, and I will keep it for reference. But lets try again. :robotlol:
It might help if I give as much context as I can. This is based on a report for invoices that have problems with them. I receive this report weekly. When a given invoice stops showing up on the invoice, it means it is closed, or completed. I am tasked with creating a dashboard that shows which invoices are open, and how long they have been open. Once they’re closed, I need to be able to count how many weeks it took for the problematic invoice to be closed.
Inv# Report Date Max Report Date Column I want
1 7/29/2019 9/2/2019 Pending 1 8/5/2019 9/2/2019 Pending 1 8/12/2019 9/2/2019 Pending 1 8/26/2019 9/2/2019 Pending 1 9/2/2019 9/2/2019 Pending 2 7/29/2019 9/2/2019 Complete 2 8/5/2019 9/2/2019 Complete 2 8/12/2019 9/2/2019 Complete 3 7/29/2019 9/2/2019 Complete 3 8/5/2019 9/2/2019 Complete 3 8/12/2019 9/2/2019 Complete 3 8/26/2019 9/2/2019 Complete 4 7/29/2019 9/2/2019 Complete 5 8/5/2019 9/2/2019 Complete 5 8/12/2019 9/2/2019 Complete 6 8/26/2019 9/2/2019 Pending 6 9/2/2019 9/2/2019 Pending 7 9/2/2019 9/2/2019 Pending 8 9/2/2019 9/2/2019 Pending I’ve made a small sample dataset for you. The goal is, using Columns A, B, C, create a Power Query that returns me the values in column D.
Given the context of the project, if you believe this approach is not the solution, please help explain how I should be solving this logic.
Thanks again for your help,
-DK
P.S. is there a better way for me to attach a table to a response?