cancel
Showing results for
Did you mean:
Frequent Visitor

## HELP!!!!! Return the most recent text value per group based on date

Hi,

I'd like to get some help on an efficient DAX solution for calculating the most recent value (text) per group.

As the below picture shows, I have the customers' visiting history by date by location. I am looking for a solution to show the most recent visited store's information per customer.

Since the store column is a text datatype, I am not able to utilize the MIN/ MAX function so well around that.

2 ACCEPTED SOLUTIONS
Community Support

Hi @jts_ ,

Try to create a measure like so:

``````Latest Store =
MAXX (
TOPN (
1,
ALLEXCEPT ( 'Table', 'Table'[Customer] ),
CALCULATE ( MAX ( 'Table'[Date] ) ), DESC
),
[Store]
)
``````

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

Hi,

You may refer to my solution in this file.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
4 REPLIES 4
Super User

Hi @jts_

Can you share the sample of your data in a text format here to be able to copy and paste that?

Frequent Visitor

Hi @VahidDM ,

Thanks for your response. Sure thing- Please see below for the text version of the data. Text in red are the result that I wanted to get. Thank you!

Date       Customer   Store

1/1/2022   Amy         A

3/1/2022   Amy         A

5/1/2022   Amy         C

-------------------------------------

2/1/2022   Brenda     D

3/1/2022   Brenda     A

-------------------------------------

4/1/2022   Carmela   B

5/1/2022   Carmela   A

6/1/2022   Carmela   B

Super User

Hi,

You may refer to my solution in this file.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @jts_ ,

Try to create a measure like so:

``````Latest Store =
MAXX (
TOPN (
1,
ALLEXCEPT ( 'Table', 'Table'[Customer] ),
CALCULATE ( MAX ( 'Table'[Date] ) ), DESC
),
[Store]
)
``````

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements