Previous invoice number (Looking for more efficient calculation)

[Anonymous]

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?

[Zubair_Muhammad]

Hi @Anonymous

See if this is slightly faster

Previous Invoice V1 =
VAR PreviousInvoice = Sales[InvoiceID]
RETURN
    CALCULATE (
        MAX ( Sales[InvoiceID] ),
        FILTER (
            ALLEXCEPT ( Sales, Sales[ClientID] ),
            Sales[InvoiceID] < PreviousInvoice
        )
    )

[Anonymous]

I'm not noticing the same memory consumption using ALLEXPECT in both methods (consumption is just flat), but PBI seems stuck on "working on it". - Left it for 5 min before shutting the application down (if it actuallly works, then it's to inefficient anyways).

Thought about that instead of passing through the entire Sales table, I could maybe use a SUMMARIZECOLUMNS based on InvoiceID and Client ID, filtered by ClientID for that row, so PBI would need to pass through fever records?

Is that possible in a calculated column and how so?

[Zubair_Muhammad]

@Anonymous

How about a MEASURE instead of Calculated Column?

[Anonymous]

I need to use the output for filtering which is why I've opted for a calculated column.

My goal is to find all the invoices that contain some specific items, and based on those invoice find each clients previous invoice from that.

My process should then be:

1. Find invoices that contains items x,y,z

2. Find each clients previous invoice before the invoice that contained x,y,z

3. Find the revenue of all previous invoices.

If you can achieve the same in a measure, then fine by me.

[Zubair_Muhammad]

@Anonymous

Lets give it a try. It may make things faster since calculated columns consume memory and disk space

Try this MEASURE

Previous Invoice V1 =
VAR PreviousInvoice =
    SELECTEDVALUE ( Sales[InvoiceID] )
RETURN
    CALCULATE (
        MAX ( Sales[InvoiceID] ),
        FILTER (
            ALLEXCEPT ( Sales, Sales[ClientID] ),
            Sales[InvoiceID] < PreviousInvoice
        )
    )

[Anonymous]

Unfortunatly that doesn't work.

Filtered out a single customer and couldn't get PowerBI to turn out a result.

Think I'm going to try with the summarizecolumns approach and see what happens.

[Vvelarde · Accepted solution]

@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

[Anonymous]

Great contribution!

Going to try and implement it now.

Would you need to add a table buffer to ensure the sorting is maintained, or is that fixed through the merging?