Calculate the difference in created date and resolved date

[Learnpbi]

hi Team

I have two columns in the sample set provided below, both are these date columns with date and time stamp.

What do I want to calculate

1) Calculate the average time it took John or Tim to resolve all queries (please note assignee column is not complete in real data set there will always be value in this column).

2) In real data set resolved column can be blank, and when it is blank then it average value calculated above should not include this in the calculation.

Regards

Power Bi Learner

[v-juanli-msft · Accepted solution]

Hi @Learnpbi

Create calculated columns

difference = DATEDIFF([Created],[Resloved],MINUTE)

average = AVERAGEX(FILTER(ALL(Sheet5),AND(OR([Assignee]="John",[Assignee]="Tim"),[Resloved]<>BLANK())),[difference])

Or 

average2 = CALCULATE(AVERAGE(Sheet5[difference]),FILTER(ALL(Sheet5),[Assignee]="John"||[Assignee]="Tim"&&[Resloved]<>BLANK()))

Best Regards

maggie