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.
I have two columns that I'm comparing: an open time and close time for an issue that clients have logged.
I'm trying to produce a column that tells me how long each issue was open for. Simple right? Make a column, enter formula: close time - open time.
However our work days are 9-5:30 Mon-Fri. So if a call was opened at 4pm on a Friday and closed at 10am on a Monday I want it to tell me the call was open for 2.5 hours (as that was the amount of time it could possibly have been worked on), not 66 hours.
Can anyone help? Is there a pre-exisiting function built into PowerBI that I can use to do this?
Solved! Go to Solution.
Hi @RobMiles ,
One sample for your reference. Here we can create the measure as below to get the excepted result.
Measure = VAR cal = FILTER ( CALENDAR ( MAX ( 'Table1'[log date] ), MAX ( Table1[close date] ) ), WEEKDAY ( [Date], 2 ) <= 5 ) VAR logdat = DATE ( YEAR ( MAX ( Table1[log date] ) ), MONTH ( MAX ( Table1[log date] ) ), DAY ( MAX ( Table1[log date] ) ) ) VAR closedate = DATE ( YEAR ( MAX ( Table1[close date] ) ), MONTH ( MAX ( Table1[close date] ) ), DAY ( MAX ( Table1[close date] ) ) ) VAR starttime = TIME ( 9, 0, 0 ) VAR endtime = TIME ( 17, 30, 0 ) VAR day = COUNTROWS ( cal ) - 2 RETURN IF ( day <= 0, DATEDIFF ( MAX ( Table1[log date] ), logdat + endtime, MINUTE ) + DATEDIFF ( closedate + starttime, MAX ( Table1[close date] ), MINUTE ), IF ( day > 0, day * 8.5 * 60 + DATEDIFF ( MAX ( Table1[log date] ), logdat + endtime, MINUTE ) + DATEDIFF ( closedate + starttime, MAX ( Table1[close date] ), MINUTE ) ) ) / 60
Hi @RobMiles ,
One sample for your reference. Here we can create the measure as below to get the excepted result.
Measure = VAR cal = FILTER ( CALENDAR ( MAX ( 'Table1'[log date] ), MAX ( Table1[close date] ) ), WEEKDAY ( [Date], 2 ) <= 5 ) VAR logdat = DATE ( YEAR ( MAX ( Table1[log date] ) ), MONTH ( MAX ( Table1[log date] ) ), DAY ( MAX ( Table1[log date] ) ) ) VAR closedate = DATE ( YEAR ( MAX ( Table1[close date] ) ), MONTH ( MAX ( Table1[close date] ) ), DAY ( MAX ( Table1[close date] ) ) ) VAR starttime = TIME ( 9, 0, 0 ) VAR endtime = TIME ( 17, 30, 0 ) VAR day = COUNTROWS ( cal ) - 2 RETURN IF ( day <= 0, DATEDIFF ( MAX ( Table1[log date] ), logdat + endtime, MINUTE ) + DATEDIFF ( closedate + starttime, MAX ( Table1[close date] ), MINUTE ), IF ( day > 0, day * 8.5 * 60 + DATEDIFF ( MAX ( Table1[log date] ), logdat + endtime, MINUTE ) + DATEDIFF ( closedate + starttime, MAX ( Table1[close date] ), MINUTE ) ) ) / 60
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |