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

[jts_]

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.

Looking forward to any creative comments! Thank you in advance!!

[Icey · Accepted solution]

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.

[VahidDM]

Hi @jts_ 

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[jts_]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in this file.

Hope this helps.

[Icey · Accepted solution]

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.