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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors