Forum Discussion
Solved
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 |
Please help me with it
Latest Completed Date = CALCULATE ( MAX ( 'Table'[completed date] ), ALLEXCEPT ( 'Table', 'Table'[service id] ) )
4 Replies
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])Latest Completed Date = CALCULATE ( MAX ( 'Table'[completed date] ), ALLEXCEPT ( 'Table', 'Table'[service id] ) )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!!rajendraongole1 hi sir ,can you share the pbix file for it. formula not working for ,me
