Return Text Value Based on Client ID and Last Date

[samdep]

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!

[bbwong]

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?

[HomerSimpson123]

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]

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

    )

[samdep]

Can you share your code, plus some sample data?

[bbwong]

Hi samdep,

My sample data is:

Client ID	Purchase Date	ProductName ID
4001	21/07/2022	Pdt ID-199
4001	20/07/2022	Pdt ID-200
4001	19/07/2022	Pdt ID -202
4001	18/07/2022	Pdt ID -201

Output:

Client ID	Purchase Date	ProductName ID
4001	21/07/2022	Pdt 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.

[amitchandak · Accepted solution]

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

[HomerSimpson123]

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.

[samdep]

@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!