Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
PowerrrBrrr
Helper III
Helper III

How to get the latest date value from the column

I have following table and I need the repeated row with latest date. I need only those values which was signed latest

 

                                Name      User                                  Date Checked Out
                                ABC    OOO                                    2017-12-14T11:47:00
                              ABC  XXX                                    2017-12-14T11:57:00
                              ABC  HHH                                    2017-12-14T11:17:00
                               DEF  VAA                                    2017-12-17T11:47:00
                               DEF   KAS                                    2017-12-17T11:00:00

 

Here I need result like below:

                                           ABC  XXX                                2017-12-14T11:57:00
                                           DEF  VAA                                2017-12-17T11:47:00

 

Can someone please help?

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

Try:

Latest date by name =
VAR _LatestDate = CALCULATE(MAX(Table[Date Checked out]), ALLEXCEPT (Table, Table[Name]))

RETURN

COUNTROWS (SUMMARIZE (FILTER(Table, Table[Date Checked out] = _LatestDate), Table[Name], Table[User, Table[Date Checked Out])

 

add the three fields to a table visual, add the [Latest date by name] measure to the filter pane and set the value to equals 1





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
PaulDBrown
Community Champion
Community Champion

Try:

Latest date by name =
VAR _LatestDate = CALCULATE(MAX(Table[Date Checked out]), ALLEXCEPT (Table, Table[Name]))

RETURN

COUNTROWS (SUMMARIZE (FILTER(Table, Table[Date Checked out] = _LatestDate), Table[Name], Table[User, Table[Date Checked Out])

 

add the three fields to a table visual, add the [Latest date by name] measure to the filter pane and set the value to equals 1





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






ahmadibrahimbus
Resolver III
Resolver III

Table 2 =
SUMMARIZE( 'Table',
   
    'Table'[ Name],
    "__MaxDate_Checke_Out",
        CALCULATE (
            MAX ('Table'[Date Checked Out]),
            REMOVEFILTERS ( 'Table'[Date Checked Out])),"User",CALCULATE(MAX('Table'[      User]),'Table'[Date Checked Out]=MAX ('Table'[Date Checked Out])
            ))
could you please try the summarize concept and let me know if it's solved your need.
jgeddes
Super User
Super User

I am sure there are other ways to do this but you can add a column to your table that determines if the Date Checked Out is the lastest for that name

Latest =
IF(
    CALCULATE(MAX(checkOutTable[Date Checked Out]),ALLEXCEPT(checkOutTable,checkOutTable[Name])) = [Date Checked Out],
    "Latest",
    "Not Latest"
)
 
Then use that column in as criteria in a measure
 
Latest Check Out Date =
CALCULATE(
    MAX(checkOutTable[Date Checked Out]),
    checkOutTable[Latest] = "Latest"
)
 
You should end up with
jgeddes_0-1664480615329.png






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.