Previous invoice number (Looking for more efficient calculation)

Hi,

I have a sales table with around 1 mio. records, where each row is one item row on each individuel invoice.

The invoices are created in a numerical order, so the newest will always have the highest number.

For filtering purposes - thus calculating in a calculated column - I'm trying to retrieve each clients previous invoice number to the current invoice row.

For this purpose I've so far tried two different approaches, which both work on a small sample size, but as soon as I implement the same measure on the main 1 mio. row table, all memory is used up, Power BI is stuck on "Making changes" or simply just crashes.

Method 1 using a variable:

Previous Invoice V1 =
VAR
PreviousInvoice = Sales[InvoiceID]
RETURN
CALCULATE( MAX(Sales[InvoiceID] ) ;
            VALUES( Sales[ClientID] ) ;
            Filter( All(Sales); Sales[InvoiceID] < PreviousInvoice
            )

Method 2 using EARLIER:

Previous Invoice v2 = 
 CALCULATE( MAX(Sales[InvoiceID]) ;
     VALUES(Sales[InvoiceID]);
     FILTER(ALL(Sales); Sales[InvoiceID] < EARLIER(Sales[InvoiceID]) )
 )

I thought about trying to achieve the same in Power Query, but so far haven't come up with a solution.

Anyone care to suggest a different approach that would avoid the memory issue?

Need Help

Vvelarde
8 years ago
Anonymous

Hi, lets try with Query Editor:

Step 1: Sort your Data

Step 2: Add a Index Column

Step 3: Add a Custom Column with the PrevIndex (Index - 1)

Step 4: Merge the Query Using Index and PrevIndex Columns

Step 5: Expand the Custom Column (only the Invoice ID) ***The image has a wrong column

Step 6: The result is:

Step 7: Close & Apply

Step 8: Go to Data Tab

I don't review why happen this but the Null PreInvoiceID change to the last InvoiceID of the ClientID. So need a little flix.

Step 9: Add a calculated column to evaluate this condition.

Ready. I hope helps in your case.

Regards

Victor
Lima - Peru