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

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

          )