Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
@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.
IS this correct:
Column
&& DATEDIFF('table'[creation date],'table'[update date],MINUTE)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |