topic Re: Return Text Value Based on Client ID and Last Date in DAX Commands and Tips

Return Text Value Based on Client ID and Last Date

samdep — Tue, 18 May 2021 14:57:52 GMT

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!

Re: Return Text Value Based on Client ID and Last Date

amitchandak — Tue, 18 May 2021 15:46:40 GMT

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 )

Re: Return Text Value Based on Client ID and Last Date

samdep — Wed, 19 May 2021 14:57:07 GMT

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!

Re: Return Text Value Based on Client ID and Last Date

bbwong — Fri, 29 Jul 2022 04:45:39 GMT

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?

Re: Return Text Value Based on Client ID and Last Date

samdep — Fri, 29 Jul 2022 23:48:53 GMT

Can you share your code, plus some sample data?

Re: Return Text Value Based on Client ID and Last Date

bbwong — Sun, 31 Jul 2022 22:26:49 GMT

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.

Re: Return Text Value Based on Client ID and Last Date

HomerSimpson123 — Wed, 06 Sep 2023 13:50:20 GMT

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.

Re: Return Text Value Based on Client ID and Last Date

HomerSimpson123 — Wed, 06 Sep 2023 13:51:21 GMT

Hi,

The measure

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.

Re: Return Text Value Based on Client ID and Last Date

bbwong — Thu, 07 Sep 2023 06:41:47 GMT

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

)