Forum Discussion

samdep's avatar
samdep
Icon for Advocate II rankAdvocate II
5 years ago
Solved

Return Text Value Based on Client ID and Last Date

Hi All - 

 

I have scoured the web looking for an answer on this topic and while there are lots of tutorials/solutions for finding last/max date by customerID (for a purchase, visit, survey taken, etc.), I can't find anything that goes one step further -- and looks at clientID and the last/max date associated with that client ID, then returns the name of that last item purchased or name of the survey they took.

 

So, for example -- sample data:

 

ClientID   PurchaseDate  ItemPurchased

123          1/1/2021         Shirt

123          1/5/2021         Pants

456          1/11/2021       Shoes

456          1/25/2021       Book

 

I'm looking to turn the above data into a table that shows the client's last/max/latest purchase date and the item purchased associated with that last date - so, the output would be:

 

Client  Last Purchase Date   ItemPurchased

123      1/5/2021                  Pants

456      1/25/2021                Book

 

Finding the last/max date either via a calc column or measure is no problem, but any help as far as retrieving the actual item purchased associated with that date would be so helpful.

 

thank you!

 

  • amitchandak's avatar
    amitchandak
    5 years ago

    samdep , use this measure for ItemPurchased, if needed take max of date

     

    Measure =
    VAR __id = MAX ('Table'[ClientID] )
    VAR __date = CALCULATE ( MAX('Table'[PurchaseDate] ), ALLSELECTED ('Table' ), 'Table'[ClientID] = __id )
    CALCULATE ( max ('Table'[ItemPurchased] ), VALUES ('Table'[ClientID] ),'Table'[ClientID] = __id,'Table'[PurchaseDate] = __date )

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    samdep , use this measure for ItemPurchased, if needed take max of date

     

    Measure =
    VAR __id = MAX ('Table'[ClientID] )
    VAR __date = CALCULATE ( MAX('Table'[PurchaseDate] ), ALLSELECTED ('Table' ), 'Table'[ClientID] = __id )
    CALCULATE ( max ('Table'[ItemPurchased] ), VALUES ('Table'[ClientID] ),'Table'[ClientID] = __id,'Table'[PurchaseDate] = __date )

    • samdep's avatar
      samdep
      Icon for Advocate II rankAdvocate II
      5 years ago

      amitchandak - thank you so much! I have been trying to figure this out for a while now to use within multiple dashboards - and your suggested measure worked perfectly. thank you!

    • HomerSimpson123's avatar
      HomerSimpson123
      Frequent Visitor
      3 years ago

      Hi,

      The measure 

      Measure =
      VAR __id = MAX ('Table'[ClientID] )
      VAR __date = CALCULATE ( MAX('Table'[PurchaseDate] ), ALLSELECTED ('Table' ), 'Table'[ClientID] = __id )
      CALCULATE ( max ('Table'[ItemPurchased] ), VALUES ('Table'[ClientID] ),'Table'[ClientID] = __id,'Table'[PurchaseDate] = __date )

       

      Always shows an error with the calculate - is there something wrong with the above measure where the last calculate shows an error. 

       

      Thanks in advanced.

  • bbwong's avatar
    bbwong
    Icon for Helper I rankHelper I
    4 years ago

    Hi,

     

    This formula is exactly what I need however it keeps telling me the syntax for CALCULATE is incorrect.

    Any ideas how to overcome this?

     

     

    • samdep's avatar
      samdep
      Icon for Advocate II rankAdvocate II
      4 years ago

      Can you share your code, plus some sample data?

      • bbwong's avatar
        bbwong
        Icon for Helper I rankHelper I
        4 years ago

        Hi samdep,

         

        My sample data is:

        Client IDPurchase DateProductName ID
        400121/07/2022Pdt ID-199
        400120/07/2022Pdt ID-200
        400119/07/2022Pdt ID -202
        400118/07/2022Pdt ID -201

         

        Output:

        Client IDPurchase DateProductName ID
        400121/07/2022Pdt ID-199

         

        Code is:

        Measure =
        VAR __id = MAX ('Table'[ClientID] )
        VAR __date = CALCULATE ( MAX('Table'[PurchaseDate] ), ALLSELECTED ('Table' ), 'Table'[ClientID] = __id )
        CALCULATE ( max ('Table'['ProductName ID] ), VALUES ('Table'[ClientID] ),'Table'[ClientID] = __id,'Table'[PurchaseDate] = __date )

        Appreciate your assistance.

         

    • HomerSimpson123's avatar
      HomerSimpson123
      Frequent Visitor
      3 years ago

      Hi,

      I also have an similar issue but keep getting an error with the calculate. Did you manage to find a solution for the calculate error? I don't think anyone replied back for a full response to the calculate error in the below measure....

       

      Did you finally get a solution? Could you share if possible? 

       

      Thanks in advanced.

       

      Measure =
      VAR __id = MAX ('Table'[ClientID] )
      VAR __date = CALCULATE ( MAX('Table'[PurchaseDate] ), ALLSELECTED ('Table' ), 'Table'[ClientID] = __id )
      CALCULATE ( max ('Table'[ItemPurchased] ), VALUES ('Table'[ClientID] ),'Table'[ClientID] = __id,'Table'[PurchaseDate] = __date )

       

      • bbwong's avatar
        bbwong
        Icon for Helper I rankHelper I
        3 years ago

        Hi,

         

        You could try this:

         

        Measure =

        VAR __id = MAX('Table'[ClientID])

        VAR __date =

            CALCULATE(

                MAX('Table'[PurchaseDate]),

                ALLSELECTED('Table'),

                'Table'[ClientID] = __id

            )

        RETURN

            CALCULATE(

                MAX('Table'[ItemPurchased]),

                'Table'[ClientID] = __id,

                'Table'[PurchaseDate] = __date

            )