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

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's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      Zubair_Muhammad
      Community Champion
      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
        )
    )