Forum Discussion

kaledjeff's avatar
kaledjeff
Advocate 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
Microsoft 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
    Microsoft Employee
    8 years ago

    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. :)