Forum Discussion

Learnpbi's avatar
Learnpbi
Regular Visitor
8 years ago
Solved

Calculate the difference in created date and resolved date

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's avatar
    v-juanli-msft
    8 years ago

    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

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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