cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors