cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jts_
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.

 

explain.PNG

 

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

2 ACCEPTED SOLUTIONS
Icey
Community Support
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]
)

Icey_0-1655776667976.png

 

Best Regards,

Icey

 

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

View solution in original post

Hi,

You may refer to my solution in this file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
VahidDM
Super User
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?

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

jts_
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

 

Hi,

You may refer to my solution in this file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Icey
Community Support
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]
)

Icey_0-1655776667976.png

 

Best Regards,

Icey

 

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.