Previous Purchase

[kaledjeff]

Hello,

I have a list of purchase orders for all of our customers. I would like to know how to get the previous purchase order for a customer.

Please keep in mind the numbers of purchase orders are randomly generated.

I very much appreaciate your help.

BR

[Phil_Seamark]

Hi @kaledjeff

This calculated column is close

Previous Order (Desired Result) = 
VAR myLastDate =    
    CALCULATE(
           MAX('Table1'[Purchase Date]),
           FILTER(
               'Table1','Table1'[Client] = EARLIER('Table1'[Client]) && 
               'Table1'[Purchase Date] < EARLIER('Table1'[Purchase Date])
           )
          )
RETURN CALCULATE(
            MAX('Table1'[Purchase Order]),
            FILTER('Table1','Table1'[Purchase Date] = myLastDate)
            )     

[kaledjeff]

Thank you @Phil_Seamark @Zubair_Muhammad for help.

I tested the calculated column by @Zubair_Muhammad and it worked like a charm.

@Phil_Seamark, I will test the function and let you know.

I appreacite your help.

BR

[Phil_Seamark]

HI @kaledjeff

They are essentially the same code, except you don't actually need to use the ALLEXCEPT function.  It doesn't do anything in calculated columns  - so could just slow down the calculation. 🙂

[Phil_Seamark]

Hi @kaledjeff

This doesn't grab the MAX purchase order number.  It uses the MAX function to find the newest of all purchase orders in the past.  It should give you what you need. 🙂

[NiMP]

Hi @Phil_Seamark

I agree with you !

Your formula works while the one of @Zubair_Muhammad give me an error message : not enough memory to proceed.

Your formula helps me a lot but I steel have an issue.

It sum all "previous order" if there have been several order the same day.

Is there a way to retrieve only the last amount without summing all of them ?

I try to integrate LASTNONBLANK in your formula without success.

Any idea will be appreciated 🙂

Have a nice day

Nicolas

[kaledjeff]

Thank you very much for quick reply.

But it isn't exactly what I need. The purchase number is created randomly. I can't take the max.

BR

[Zubair_Muhammad]

@kaledjeff

I think @Phil_Seamark column should work.

Nevertheless try the following as well

First a MEASURE

Measure =
VAR myLastDate =
    CALCULATE (
        MAX ( 'Table1'[Purchase Date] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Client] ),
            'Table1'[Purchase Date] < SELECTEDVALUE ( 'Table1'[Purchase Date] )
        )
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( 'Table1'[Purchase Order], 1 ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Client] ),
            'Table1'[Purchase Date] = myLastDate
        )
    )

[Zubair_Muhammad · Accepted solution]

@kaledjeff

Or this Calculated Column

Column =
VAR myLastDate =
    CALCULATE (
        MAX ( 'Table1'[Purchase Date] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Client] ),
            'Table1'[Purchase Date] < EARLIER ( 'Table1'[Purchase Date] )
        )
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( 'Table1'[Purchase Order], 1 ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Client] ),
            'Table1'[Purchase Date] = myLastDate
        )
    )