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 orders are randomly generated.

 

 

 

 

 

 

 

 

 

I very much appreaciate your help.

 

BR

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

8 Replies

  • 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

       

      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

    • 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
        )
    )

         

         

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