Forum Discussion

kaledjeff's avatar
kaledjeff
Icon for Advocate I rankAdvocate I
8 years ago
Solved

Previous Purchase

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 order...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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
        )
    )
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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)
            )
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
    NiMP
    Regular Visitor
    7 years ago

    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