latest date column needed

[Ashik008]

Hi all,

I need help with data .

I have table with id and date data. I need a column with latest date if the id

service id	completed date
101	6/20/2024
101	5/10/2023
102	10/10/2023
103	1/1/2023
103	1/1/2024

i need the result 

service id	completed date	latest complete date
101	6/20/2024	6/20/2024
101	5/10/2023	6/20/2024
102	10/10/2023	10/10/2023
103	1/1/2023	1/1/2024
103	1/1/2024	1/1/2024

Please help me with it

[AntrikshSharma · Accepted solution]

@Ashik008 

Latest Completed Date = 
CALCULATE ( 
    MAX ( 'Table'[completed date] ),
    ALLEXCEPT ( 'Table', 'Table'[service id] )
)

[rajendraongole1]

Hi @Ashik008 - you can use max function on completed date and filter based on ID with current id.

Calculated columns as below:

LatestCompleteDate =
VAR CurrentServiceID = ServiceData[service id]
RETURN
CALCULATE(
MAX(ServiceData[completed date]),
FILTER(
ServiceData,
ServiceData[service id] = CurrentServiceID
)
)

Report view:

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Ashik008]

@rajendraongole1 hi sir ,can you share the pbix file for it. formula not working for ,me

[ajohnso2]

Something like this should work:

Latest Completion Date =
VAR _ServiceID = SELECTEDVALUE('YourTable'[Service ID])
VAR _DateCompleted =

FILTER(
    ADDCOLUMNS(
        CALCULATETABLE(
            ALL('YourTable'[Service ID], 'YourTable'[Date Completed])
        ),
        "@MaxCompletedDate", 'YourTable'[Date Completed]
    ),
    'YourTable'[Service ID] = _ServiceID
)

RETURN
MAXX(_DateCompleted, [@MaxCompletedDate])