Forum Discussion
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 an example of a single invoice that is still appearing on the report. I want to have a column that for all lines reads ‘pending’ if the max report date of an invoice # is equal to the most recent report date. If it is less than the most recent report date, I want it to read "complete" for all lines.
I have a few custom Columns I have been playing with:
Current Date = MAX('Complete & Pending'[Reporting Date ])
Complete Pending = IF('Complete & Pending'[Reporting Date ].[Date] < 'Complete & Pending'[Current Date], "Complete", "Pending")
For instance,
Since the max [Reporting Date] and the [Current Date] match for this invoice, I want the 4th column to read ‘pending’ for all the lines. Any ideas?
Thanks
14 Replies
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" )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")
