topic Re: latest date column needed in DAX Commands and Tips

latest date column needed

Ashik008 — Thu, 20 Jun 2024 14:53:46 GMT

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

Re: latest date column needed

ajohnso2 — Thu, 20 Jun 2024 15:05:20 GMT

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])

Re: latest date column needed

rajendraongole1 — Thu, 20 Jun 2024 15:06:22 GMT

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!!

Re: latest date column needed

Ashik008 — Thu, 20 Jun 2024 16:05:50 GMT

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

Re: latest date column needed

AntrikshSharma — Thu, 20 Jun 2024 17:26:55 GMT

Ashik008

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