DATEDIFF BETWEEN TWO DATE in order to calculate resolution time

[ayoubb]

Hello, 

First of all thank you for your support and assistance, 

Please i have this table below: 

And i want to calculate the average resolution time in this format "hh:mm:ss" but only for the request number which they have been created between 06h and 19h?

[v-xulin-mstf · Accepted solution]

Hi @ayoubb 

Create a measure as:

Measure =

var _table=

SUMMARIZE('Table','Table'[Request number],"start",

var _start=

CALCULATE(

    MAX('Table'[creation date]),

    FILTER(

        ALL('Table'),

        'Table'[Request number]=MAX('Table'[Request number]) && 'Table'[Status]="resolved" && HOUR('Table'[creation date])>6

    )

)



var _end=

CALCULATE(

   MAX('Table'[update date]),

   FILTER(

       'Table',

       'Table'[Request number]=MAX('Table'[Request number]) && 'Table'[Status]="resolved" && HOUR('Table'[update date])<19

   )

)

return DATEDIFF(_start,_end,SECOND)

)

return SUMX(_table,[start])/COUNTROWS(_table)

Here is the output:

Here is the demo, please try it:DATEDIFF BETWEEN TWO DATE in order to calculate resolution time

Best Regards,

Link

[Greg_Deckler]

@ayoubb If you can post that data as text could provide a specific solution. But, in the mean time here are a number of articles on duration calculations:

(1) Chelsie Eiden's Duration - Microsoft Power BI Community

Duration to Seconds Converter - Microsoft Power BI Community

By simply subtracting your two time entries like this ([update date] - [creation date]) * 1. , you will get a decimal value where the integer portion is the number of days and your decimal portion is fractions of a day. So 1/24 hours, 1/60 is minutes, 1/3600 seconds, etc.

[ayoubb]

IS this correct:

Column

1. Average resolution time = CALCULATE(DISTINCTCOUNT('table'[request number]), FILTER('table','table'[status] IN {"resolved"}))

&& DATEDIFF('table'[creation date],'table'[update date],MINUTE)