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 )

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.