Rows - Maximum Date

[jerryr]

Hi -

I have a table and I woudl like to only keep the rows with the maximum date by a specific column.

Example:

Starting data : TableTransactions

DepartmentID	Date	TransactionID
A	01/31/2025	123
A	11/01/2025	345
B	10/10/2025	543
B	03/03/2025	778
C	10/01/2025	127

Endingg data : TableTransactions

DepartmentID	Date	TransactionID
A	11/01/2025	345
B	10/10/2025	543
C	10/01/2025	127

Any thoughts ? Appreciate it - Jerry

[mdaatifraza5556 · Accepted solution]

Hi @jerryr 

Can you please try the below DAX

I have created the measure using below dax .

Show Latest =

IF (

    MAX ( data[Date] ) =

        CALCULATE (

            MAX ( data[Date] ),

            ALLEXCEPT ( data, data[DepartmentID] )

        ),

    1,

    0

)

Create a table visual and add all the column u want in as it shows in your result

then click on the visual and then go for visual level filter ( Set Show Latest = 1).

If this answers your questions, kindly accpet it as a solution and give kudos.

[v-prasare]

Hi @jerryr,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by community members for your issue worked for you or let us know if you need any further assistance?

Your feedback is important to us, Looking forward to your response

Thanks,

Prashanth

[v-prasare]

Hi @jerryr,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by community members for your issue worked for you or let us know if you need any further assistance?

@Chewdata, @m_dekorte, @PhilipTreacy& @Zanqueta, thanks for your prompt response

Your feedback is important to us, Looking forward to your response

Thanks,

Prashanth

[Chewdata]

Hey,

This method checks against the max date for that apartment ID and then for the largest Transaction ID. If there are multiple records for that date for that departmentID. If you remove the filter_TransactionID, you get filtering only on date.

let
    // replace YOURDATA with your source
    Source = YOURDATA,

    // select rows that match the Max date for that DepartmentID
    filter_maxDate = Table.SelectRows(
        Source, 
        each ([Date] = List.Max(Table.SelectRows(Source, (Row) => Row[DepartmentID] = _[DepartmentID])[Date]))
        ),

    // select rows that match the Max transactionID for the DepartmentID
    filter_maxTransactionID = Table.SelectRows(
        filter_maxDate, 
        each ([TransactionID] = List.Max(Table.SelectRows(filter_maxDate, (Row) => Row[DepartmentID] = _[DepartmentID])[TransactionID]))
        )
        
in
    filter_maxTransactionID

Hope this helps!
If any of these solutions helps you, please consider to give kudo's and accept an answer so other users with similar problems can find the answer quickly!

[m_dekorte]

Here's how you can lookup the record with max date, assuming there is only 1 row per date.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc1RCsAgDAPQu/RbaNpa3O+2Y0jvfw07FRnkJzxCeqebCkHYhBXqWUSNoiwQYRyw6hOeD8CZDV7tAIwzG1q7Jrxrgd9Ho4gB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DepartmentID = _t, Date = _t, TransactionID = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"DepartmentID", type text}, {"Date", type date}, {"TransactionID", Int64.Type}}, "en-US"),
    GroupRows = Table.Group(ChType, {"DepartmentID"}, {{"All", each _{[Date=List.Max([Date])]}, type [DepartmentID=nullable text, Date=nullable date, TransactionID=nullable number]}}),
    ExpandFields = Table.ExpandRecordColumn(GroupRows, "All", {"Date", "TransactionID"}, {"Date", "TransactionID"})
in
    ExpandFields

alternatively, this will get you all rows with the max date

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc1RCsAgDAPQu/RbaNpa3O+2Y0jvfw07FRnkJzxCeqebCkHYhBXqWUSNoiwQYRyw6hOeD8CZDV7tAIwzG1q7Jrxrgd9Ho4gB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DepartmentID = _t, Date = _t, TransactionID = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"DepartmentID", type text}, {"Date", type date}, {"TransactionID", Int64.Type}}, "en-US"),
    GroupRows = Table.Group(ChType, {"DepartmentID"}, {{"All", each Table.SelectRows(_, (x)=> x[Date]=List.Max([Date])), type table [DepartmentID=nullable text, Date=nullable date, TransactionID=nullable number]}}),
    ExpandFields = Table.ExpandTableColumn(GroupRows, "All", {"Date", "TransactionID"}, {"Date", "TransactionID"})
in
    ExpandFields

[PhilipTreacy · Accepted solution]

Hi @jerryr 

Using Power Query/M you can do this with List.PositionOf to find the max date then extract the Date and Transaction ID.

First, Group By DepartmentID

Then create a Custom Column and use this to get the Date

= [All][Date]{List.PositionOf([All][Date], List.Max([All][Date]))}

and another Custom Column for the TransactionID

= [All][TransactionID]{List.PositionOf([All][Date], List.Max([All][Date]))}

Here's the full code and you can download it in this PBIX file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc1RCsAgDAPQu/RbaNpa3O+2Y0jvfw07FRnkJzxCeqebCkHYhBXqWUSNoiwQYRyw6hOeD8CZDV7tAIwzG1q7Jrxrgd9Ho4gB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DepartmentID = _t, Date = _t, TransactionID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DepartmentID", type text}, {"Date", type text}, {"TransactionID", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-US"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"DepartmentID"}, {{"All", each _, type table [DepartmentID=nullable text, Date=nullable date, TransactionID=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Date", each [All][Date]{List.PositionOf([All][Date], List.Max([All][Date]))}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "TransactionID", each [All][TransactionID]{List.PositionOf([All][Date], List.Max([All][Date]))}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"All"})
in
    #"Removed Columns"

Regards

Phil

[Zanqueta]

Hello @jerryr,

You can also do this in the PowerQuery query editor using Table.Group function:

Here my simulation script for your case:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc1LCsAwCATQu7gO+IuYbdtjBO9/jdpEUpiNvkHnhAsaKCMxConlwKIQbUNu+YB2W3B/JcJMgXU9QIqZAvex4KlT9P9wiHgB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DepartmentID = _t, Date = _t, TransactionID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DepartmentID"}, {{"LastDate", each List.Max([Date]), type nullable date}, {"TransactionID", each List.Max([TransactionID]), type nullable text}})
in
    #"Grouped Rows"

✅ If this response resolved your issue, please mark it as correct to assist other members of the community.

[mdaatifraza5556 · Accepted solution]

Hi @jerryr 

Can you please try the below DAX

I have created the measure using below dax .

Show Latest =

IF (

    MAX ( data[Date] ) =

        CALCULATE (

            MAX ( data[Date] ),

            ALLEXCEPT ( data, data[DepartmentID] )

        ),

    1,

    0

)

Create a table visual and add all the column u want in as it shows in your result

then click on the visual and then go for visual level filter ( Set Show Latest = 1).

If this answers your questions, kindly accpet it as a solution and give kudos.