Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey Power Users,
Solved! Go to Solution.
Hi @kbachova ,
We can use the following measure after create a axis table to meet your requriement:
Axis Table:
Measure:
Total Duration Hour =
SUMX (
'Axis Table',
VAR startHourValue = 'Axis Table'[Start Hour]
VAR endHourValue = 'Axis Table'[End Hour]
VAR hourStart =
TIME ( startHourValue, 0, 0 )
VAR hourEnd =
TIME ( endHourValue, 0, 0 )
VAR maxDuration = ( endHourValue - startHourValue ) * 60
RETURN
SUMX (
'Table',
VAR t =
CALENDAR ( 'Table'[start].[Date], 'Table'[end].[Date] )
RETURN
SUMX (
t,
VAR startTime =
TIME ( HOUR ( 'Table'[start] ), MINUTE ( 'Table'[start] ), 0 )
VAR endTime =
TIME ( HOUR ( 'Table'[end] ), MINUTE ( 'Table'[end] ), 0 )
RETURN
SWITCH (
TRUE (),
[Date] <> [start].[Date]
&& [Date] <> [end].[Date], maxDuration,
[Date] = [start].[Date]
&& [Date] = [end].[Date], SWITCH (
TRUE (),
hourEnd >= endTime
&& hourStart <= startTime, DATEDIFF ( startTime, endTime, MINUTE ),
hourEnd < startTime
|| hourStart > endTime, BLANK (),
hourEnd <= endTime
&& hourStart >= startTime, maxDuration,
hourEnd >= startTime
&& hourStart <= startTime, DATEDIFF ( startTime, hourEnd, MINUTE ),
hourStart <= endTime
&& hourEnd >= endTime, DATEDIFF ( hourStart, endTime, MINUTE ),
BLANK ()
),
[Date] = [start].[Date]
&& [Date] <> [end].[Date], IF (
hourEnd < startTime,
BLANK (),
IF (
hourStart < startTime,
DATEDIFF ( startTime, hourEnd, MINUTE ),
maxDuration
)
),
[Date] <> [start].[Date]
&& [Date] = [end].[Date], IF (
hourStart > endTime,
BLANK (),
IF ( hourEnd > endTime, DATEDIFF ( hourStart, endTime, MINUTE ), maxDuration )
),
BLANK ()
)
)
) / 60
)
If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.
Best regards,
EmployeeId | DateTimeEnd | StartDateTime | TOTAL HOURS_normal |
24 | 06/01/2020 18:10:04 +09:00 | 06/01/2020 08:37:32 +09:00 | 9.54 |
43 | 06/01/2020 18:20:03 +09:00 | 06/01/2020 08:48:53 +09:00 | 9.52 |
27 | 06/01/2020 18:24:55 +09:00 | 06/01/2020 08:14:41 +09:00 | 10.17 |
38 | 06/01/2020 18:25:17 +09:00 | 06/01/2020 08:57:03 +09:00 | 9.47 |
28 | 06/01/2020 18:25:32 +09:00 | 06/01/2020 09:16:00 +09:00 | 9.16 |
18 | 06/01/2020 18:29:39 +09:00 | 06/01/2020 08:50:07 +09:00 | 9.66 |
29 | 06/01/2020 18:44:00 +09:00 | 06/01/2020 08:54:39 +09:00 | 9.82 |
17 | 06/01/2020 19:02:24 +09:00 | 06/01/2020 08:26:54 +09:00 | 10.59 |
20 | 06/01/2020 20:13:21 +09:00 | 06/01/2020 08:14:43 +09:00 | 11.98 |
38 | 07/01/2020 18:11:00 +09:00 | 07/01/2020 08:56:15 +09:00 | 9.25 |
28 | 07/01/2020 15:11:25 +09:00 | 07/01/2020 09:11:14 +09:00 | 6.00 |
28 | 07/01/2020 18:32:37 +09:00 | 07/01/2020 15:11:30 +09:00 | 3.35 |
27 | 07/01/2020 18:08:22 +09:00 | 07/01/2020 08:56:16 +09:00 | 9.20 |
43 | 07/01/2020 18:24:48 +09:00 | 07/01/2020 08:46:33 +09:00 | 9.64 |
29 | 07/01/2020 18:46:55 +09:00 | 07/01/2020 08:55:17 +09:00 | 9.86 |
17 | 07/01/2020 19:16:25 +09:00 | 07/01/2020 08:41:29 +09:00 | 10.58 |
25 | 07/01/2020 19:28:08 +09:00 | 07/01/2020 09:00:26 +09:00 | 10.46 |
22 | 07/01/2020 19:30:06 +09:00 | 07/01/2020 08:39:56 +09:00 | 10.84 |
18 | 07/01/2020 19:58:12 +09:00 | 07/01/2020 08:53:27 +09:00 | 11.08 |
20 | 07/01/2020 20:12:55 +09:00 | 07/01/2020 08:21:24 +09:00 | 11.86 |
24 | 07/01/2020 12:00:00 +09:00 | 07/01/2020 07:40:00 +09:00 | 4.33 |
24 | 07/01/2020 17:00:00 +09:00 | 07/01/2020 12:00:00 +09:00 | 5.00 |
24 | 07/01/2020 21:10:00 +09:00 | 07/01/2020 18:00:00 +09:00 | 3.17 |
28 | 08/01/2020 18:00:00 +09:00 | 08/01/2020 09:00:00 +09:00 | 9.00 |
28 | 08/01/2020 14:05:19 +09:00 | 08/01/2020 14:05:08 +09:00 | 0.00 |
38 | 08/01/2020 18:00:55 +09:00 | 08/01/2020 08:57:56 +09:00 | 9.05 |
27 | 08/01/2020 18:04:45 +09:00 | 08/01/2020 08:49:08 +09:00 | 9.26 |
43 | 08/01/2020 18:22:03 +09:00 | 08/01/2020 08:57:04 +09:00 | 9.42 |
29 | 08/01/2020 18:26:04 +09:00 | 08/01/2020 08:49:14 +09:00 | 9.61 |
22 | 08/01/2020 18:46:32 +09:00 | 08/01/2020 08:52:35 +09:00 | 9.90 |
25 | 08/01/2020 19:06:44 +09:00 | 08/01/2020 09:03:05 +09:00 | 10.06 |
Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
employeeid | start | end | duration (hours) |
24 | 06/01/2020 18:10:04 +09:00 | 06/01/2020 08:37:32 +09:00 | 9.54 |
43 | 06/01/2020 18:20:03 +09:00 | 06/01/2020 08:48:53 +09:00 | 9.52 |
27 | 06/01/2020 18:24:55 +09:00 | 06/01/2020 08:14:41 +09:00 | 10.17 |
38 | 06/01/2020 18:25:17 +09:00 | 06/01/2020 08:57:03 +09:00 | 9.47 |
28 | 06/01/2020 18:25:32 +09:00 | 06/01/2020 09:16:00 +09:00 | 9.16 |
18 | 06/01/2020 18:29:39 +09:00 | 06/01/2020 08:50:07 +09:00 | 9.66 |
29 | 06/01/2020 18:44:00 +09:00 | 06/01/2020 08:54:39 +09:00 | 9.82 |
17 | 06/01/2020 19:02:24 +09:00 | 06/01/2020 08:26:54 +09:00 | 10.59 |
20 | 06/01/2020 20:13:21 +09:00 | 06/01/2020 08:14:43 +09:00 | 11.98 |
38 | 07/01/2020 18:11:00 +09:00 | 07/01/2020 08:56:15 +09:00 | 9.25 |
28 | 07/01/2020 15:11:25 +09:00 | 07/01/2020 09:11:14 +09:00 | 6.00 |
28 | 07/01/2020 18:32:37 +09:00 | 07/01/2020 15:11:30 +09:00 | 3.35 |
27 | 07/01/2020 18:08:22 +09:00 | 07/01/2020 08:56:16 +09:00 | 9.20 |
43 | 07/01/2020 18:24:48 +09:00 | 07/01/2020 08:46:33 +09:00 | 9.64 |
29 | 07/01/2020 18:46:55 +09:00 | 07/01/2020 08:55:17 +09:00 | 9.86 |
17 | 07/01/2020 19:16:25 +09:00 | 07/01/2020 08:41:29 +09:00 | 10.58 |
25 | 07/01/2020 19:28:08 +09:00 | 07/01/2020 09:00:26 +09:00 | 10.46 |
22 | 07/01/2020 19:30:06 +09:00 | 07/01/2020 08:39:56 +09:00 | 10.84 |
18 | 07/01/2020 19:58:12 +09:00 | 07/01/2020 08:53:27 +09:00 | 11.08 |
20 | 07/01/2020 20:12:55 +09:00 | 07/01/2020 08:21:24 +09:00 | 11.86 |
24 | 07/01/2020 12:00:00 +09:00 | 07/01/2020 07:40:00 +09:00 | 4.33 |
24 | 07/01/2020 17:00:00 +09:00 | 07/01/2020 12:00:00 +09:00 | 5.00 |
24 | 07/01/2020 21:10:00 +09:00 | 07/01/2020 18:00:00 +09:00 | 3.17 |
28 | 08/01/2020 18:00:00 +09:00 | 08/01/2020 09:00:00 +09:00 | 9.00 |
28 | 08/01/2020 14:05:19 +09:00 | 08/01/2020 14:05:08 +09:00 | 0.00 |
38 | 08/01/2020 18:00:55 +09:00 | 08/01/2020 08:57:56 +09:00 | 9.05 |
27 | 08/01/2020 18:04:45 +09:00 | 08/01/2020 08:49:08 +09:00 | 9.26 |
43 | 08/01/2020 18:22:03 +09:00 | 08/01/2020 08:57:04 +09:00 | 9.42 |
29 | 08/01/2020 18:26:04 +09:00 | 08/01/2020 08:49:14 +09:00 | 9.61 |
22 | 08/01/2020 18:46:32 +09:00 | 08/01/2020 08:52:35 +09:00 | 9.90 |
25 | 08/01/2020 19:06:44 +09:00 | 08/01/2020 09:03:05 +09:00 | 10.06 |
Hi @kbachova ,
We can use the following measure after create a axis table to meet your requriement:
Axis Table:
Measure:
Total Duration Hour =
SUMX (
'Axis Table',
VAR startHourValue = 'Axis Table'[Start Hour]
VAR endHourValue = 'Axis Table'[End Hour]
VAR hourStart =
TIME ( startHourValue, 0, 0 )
VAR hourEnd =
TIME ( endHourValue, 0, 0 )
VAR maxDuration = ( endHourValue - startHourValue ) * 60
RETURN
SUMX (
'Table',
VAR t =
CALENDAR ( 'Table'[start].[Date], 'Table'[end].[Date] )
RETURN
SUMX (
t,
VAR startTime =
TIME ( HOUR ( 'Table'[start] ), MINUTE ( 'Table'[start] ), 0 )
VAR endTime =
TIME ( HOUR ( 'Table'[end] ), MINUTE ( 'Table'[end] ), 0 )
RETURN
SWITCH (
TRUE (),
[Date] <> [start].[Date]
&& [Date] <> [end].[Date], maxDuration,
[Date] = [start].[Date]
&& [Date] = [end].[Date], SWITCH (
TRUE (),
hourEnd >= endTime
&& hourStart <= startTime, DATEDIFF ( startTime, endTime, MINUTE ),
hourEnd < startTime
|| hourStart > endTime, BLANK (),
hourEnd <= endTime
&& hourStart >= startTime, maxDuration,
hourEnd >= startTime
&& hourStart <= startTime, DATEDIFF ( startTime, hourEnd, MINUTE ),
hourStart <= endTime
&& hourEnd >= endTime, DATEDIFF ( hourStart, endTime, MINUTE ),
BLANK ()
),
[Date] = [start].[Date]
&& [Date] <> [end].[Date], IF (
hourEnd < startTime,
BLANK (),
IF (
hourStart < startTime,
DATEDIFF ( startTime, hourEnd, MINUTE ),
maxDuration
)
),
[Date] <> [start].[Date]
&& [Date] = [end].[Date], IF (
hourStart > endTime,
BLANK (),
IF ( hourEnd > endTime, DATEDIFF ( hourStart, endTime, MINUTE ), maxDuration )
),
BLANK ()
)
)
) / 60
)
If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.
Best regards,
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |