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 )

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.