TimeDifference with CONTAINSTRING

[Rana80]

Hi,

I need create a 2 dax measures that shows the average of the  difference between 2 dates in the same column for a unique task id. The start date should be the earliest date for that task id and the end date should be maximum date for that task id based on where text that contains "marked this task as resolved" in the column "messages' for that same task id.

Please be aware the message "marked this task as resolved" can be entered more than once for a task id (Eg, task id BBBB) but we need to use the latest message based on the max of the date field. The output should be formatted as HH:MM:SS. 

As mentioned it needs to be 2 measures, 1 showing the average time for the task Ids that have a Priority Flag of 'Y' and another whhere the same field in Null.

A sample from the data set i'm working for is below. 

The result i should get is for the Priority jobs the average time to complete was 00:44:13 (based on the average of task id BBBB & CCCC) and 00:09:50 (AAAA&DDDD) for where the priority flag is NULL.

Many thanks All! 🙂

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Rana80]

Hi,

Thanks for your response.

Please download link https://we.tl/t-M8iCUlcDdA

I have created the raw test data with workings and required output for PBI. Please let me know if you need anything else. 

[lbendlin]

see attached.

[Rana80]

Hi, It wasn't what i was after but i have solved this by creating a calculated table which shows my min and max date with the text condition as a single row,then created my measures from there