cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## latest date column needed

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

1 ACCEPTED SOLUTION
Super User
``````Latest Completed Date =
CALCULATE (
MAX ( 'Table'[completed date] ),
ALLEXCEPT ( 'Table', 'Table'[service id] )
)``````

4 REPLIES 4
Super User
``````Latest Completed Date =
CALCULATE (
MAX ( 'Table'[completed date] ),
ALLEXCEPT ( 'Table', 'Table'[service id] )
)``````

Super User

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!

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

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

Resolver III

Something like this should work:

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

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

RETURN
MAXX(_DateCompleted, [@MaxCompletedDate])``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.