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

     

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

       

       

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