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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Solution Sage
Solution Sage

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



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.