Is Invoice Complete vs. Pending

[Anonymous]

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

[Zubair_Muhammad]

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

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

[Zubair_Muhammad]

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

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

[Zubair_Muhammad]

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

[Anonymous]

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]

@Anonymouswith the salt!  

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

[Anonymous]

Sorry for the late reply.

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

Best

D.

[Anonymous]

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.  

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? 

[Anonymous]

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

Best
D.

[Anonymous]

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

[Anonymous]

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]

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

Best
D