Forum Discussion

PowerrrBrrr's avatar
PowerrrBrrr
Icon for Helper III rankHelper III
4 years ago
Solved

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?

Need Help
Show and Tell
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    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

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    4 years ago

    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



  • ahmadibrahimbus's avatar
    ahmadibrahimbus
    Icon for Resolver III rankResolver III
    4 years ago
    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.