Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 

otd 1.png

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")

otd 2.png

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

14 REPLIES 14
Zubair_Muhammad
Community Champion
Community Champion

@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"
)

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

 

 

@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!

otd 3.png

 

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

@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")

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

@Anonymous 

 

Power BI automatically creates one date table for each date column in the model. When you use .[Date] you refer to that table rather than the original one.

 

Here is a relevant article

 

https://www.sqlbi.com/articles/automatic-time-intelligence-in-power-bi/


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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
Not applicable

@Anonymouswith the salt! Cat LOL 

 

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? 

 

PBi.png

 

Thanks!! 

Anonymous
Not applicable

Sorry for the late reply.

 

Here's the file... Check whether or not it is what you wanted.

 

Best

D.

Anonymous
Not applicable

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

 

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

17/29/20199/2/2019Pending
18/5/20199/2/2019Pending
18/12/20199/2/2019Pending
18/26/20199/2/2019Pending
19/2/20199/2/2019Pending
27/29/20199/2/2019Complete
28/5/20199/2/2019Complete
28/12/20199/2/2019Complete
37/29/20199/2/2019Complete
38/5/20199/2/2019Complete
38/12/20199/2/2019Complete
38/26/20199/2/2019Complete
47/29/20199/2/2019Complete
58/5/20199/2/2019Complete
58/12/20199/2/2019Complete
68/26/20199/2/2019Pending
69/2/20199/2/2019Pending
79/2/20199/2/2019Pending
89/2/20199/2/2019Pending

 

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? 

Anonymous
Not applicable

 
Anonymous
Not applicable

OK. I now see exactly what you need... One moment. The code in M needs just some small adjustments.

Best
D.
Anonymous
Not applicable

@Anonymous I have the DAX version of the solution working. However, I would /love/ to see your clean M solution Robot Happy

Anonymous
Not applicable

You should NEVER use DAX for something that you can do in M. This is because of the compression level you get. With DAX it's much, much lower and hence your formulas will have more work to do when you use the column in measures.

I've attached a solution in M.

Best
Darek
Anonymous
Not applicable

I'll show you in a about an hour... when I'm back home 🙂

Best
D

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors