Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kbachova
Advocate II
Advocate II

Help in finding concept how to calculate total hours in windows based on startdattime & enddatetime

Hey Power Users,

looking for a piece of advise where to look or what concept to use.
Here is my problem:
I have a list of entries by employeeID with StartDateTIme and EndDateTime and along with calculation of the total duration (in hours) I also need to calculate how many hours were worked in these time windows per employeeID:
Window 00:00 - 05:00 W1
Window 05:00 - 09:00 W2
Window 09:00 - 18:00 W3
Window 18:00 - 22:00 W4
Window 22:00 - 24:00 W5
Does anyone have an idea how to approach/solve this? Has anyone ever dealt with similar problem? Shouls I transform the data into a different format to be able to do this type of analysis?
 
Thank you!
1 ACCEPTED SOLUTION

Hi @kbachova ,

 

We can use the following measure after create a axis table to meet your requriement:

 

Axis Table:

 

8.jpg

 

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
)

 

 

11.jpg12.jpg

 


If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
kbachova
Advocate II
Advocate II

Hey Power Users,
looking for a piece of advise where to look or what concept to use.
Here is my problem:
I have a list of entries by employeeID with StartDateTIme and EndDateTime and along with calculation of the total duration (in hours) I also need to calculate how many hours were worked in these time windows per employeeID:
Window 00:00 - 05:00 W1
Window 05:00 - 09:00 W2
Window 09:00 - 18:00 W3
Window 18:00 - 22:00 W4
Window 22:00 - 24:00 W5
Does anyone have an idea how to approach/solve this? Has anyone ever dealt with similar problem? Shouls I transform the data into a different format to be able to do this type of analysis?
 
Sample data:

 
EmployeeIdDateTimeEndStartDateTimeTOTAL HOURS_normal
2406/01/2020 18:10:04 +09:0006/01/2020 08:37:32 +09:009.54
4306/01/2020 18:20:03 +09:0006/01/2020 08:48:53 +09:009.52
2706/01/2020 18:24:55 +09:0006/01/2020 08:14:41 +09:0010.17
3806/01/2020 18:25:17 +09:0006/01/2020 08:57:03 +09:009.47
2806/01/2020 18:25:32 +09:0006/01/2020 09:16:00 +09:009.16
1806/01/2020 18:29:39 +09:0006/01/2020 08:50:07 +09:009.66
2906/01/2020 18:44:00 +09:0006/01/2020 08:54:39 +09:009.82
1706/01/2020 19:02:24 +09:0006/01/2020 08:26:54 +09:0010.59
2006/01/2020 20:13:21 +09:0006/01/2020 08:14:43 +09:0011.98
3807/01/2020 18:11:00 +09:0007/01/2020 08:56:15 +09:009.25
2807/01/2020 15:11:25 +09:0007/01/2020 09:11:14 +09:006.00
2807/01/2020 18:32:37 +09:0007/01/2020 15:11:30 +09:003.35
2707/01/2020 18:08:22 +09:0007/01/2020 08:56:16 +09:009.20
4307/01/2020 18:24:48 +09:0007/01/2020 08:46:33 +09:009.64
2907/01/2020 18:46:55 +09:0007/01/2020 08:55:17 +09:009.86
1707/01/2020 19:16:25 +09:0007/01/2020 08:41:29 +09:0010.58
2507/01/2020 19:28:08 +09:0007/01/2020 09:00:26 +09:0010.46
2207/01/2020 19:30:06 +09:0007/01/2020 08:39:56 +09:0010.84
1807/01/2020 19:58:12 +09:0007/01/2020 08:53:27 +09:0011.08
2007/01/2020 20:12:55 +09:0007/01/2020 08:21:24 +09:0011.86
2407/01/2020 12:00:00 +09:0007/01/2020 07:40:00 +09:004.33
2407/01/2020 17:00:00 +09:0007/01/2020 12:00:00 +09:005.00
2407/01/2020 21:10:00 +09:0007/01/2020 18:00:00 +09:003.17
2808/01/2020 18:00:00 +09:0008/01/2020 09:00:00 +09:009.00
2808/01/2020 14:05:19 +09:0008/01/2020 14:05:08 +09:000.00
3808/01/2020 18:00:55 +09:0008/01/2020 08:57:56 +09:009.05
2708/01/2020 18:04:45 +09:0008/01/2020 08:49:08 +09:009.26
4308/01/2020 18:22:03 +09:0008/01/2020 08:57:04 +09:009.42
2908/01/2020 18:26:04 +09:0008/01/2020 08:49:14 +09:009.61
2208/01/2020 18:46:32 +09:0008/01/2020 08:52:35 +09:009.90
2508/01/2020 19:06:44 +09:0008/01/2020 09:03:05 +09:0010.06
 

Hello @kbachova,

 

Can you provide your expected result? I am not sure about your expectations.

amitchandak
Super User
Super User

 

employeeidstartendduration (hours)
2406/01/2020 18:10:04 +09:0006/01/2020 08:37:32 +09:009.54
4306/01/2020 18:20:03 +09:0006/01/2020 08:48:53 +09:009.52
2706/01/2020 18:24:55 +09:0006/01/2020 08:14:41 +09:0010.17
3806/01/2020 18:25:17 +09:0006/01/2020 08:57:03 +09:009.47
2806/01/2020 18:25:32 +09:0006/01/2020 09:16:00 +09:009.16
1806/01/2020 18:29:39 +09:0006/01/2020 08:50:07 +09:009.66
2906/01/2020 18:44:00 +09:0006/01/2020 08:54:39 +09:009.82
1706/01/2020 19:02:24 +09:0006/01/2020 08:26:54 +09:0010.59
2006/01/2020 20:13:21 +09:0006/01/2020 08:14:43 +09:0011.98
3807/01/2020 18:11:00 +09:0007/01/2020 08:56:15 +09:009.25
2807/01/2020 15:11:25 +09:0007/01/2020 09:11:14 +09:006.00
2807/01/2020 18:32:37 +09:0007/01/2020 15:11:30 +09:003.35
2707/01/2020 18:08:22 +09:0007/01/2020 08:56:16 +09:009.20
4307/01/2020 18:24:48 +09:0007/01/2020 08:46:33 +09:009.64
2907/01/2020 18:46:55 +09:0007/01/2020 08:55:17 +09:009.86
1707/01/2020 19:16:25 +09:0007/01/2020 08:41:29 +09:0010.58
2507/01/2020 19:28:08 +09:0007/01/2020 09:00:26 +09:0010.46
2207/01/2020 19:30:06 +09:0007/01/2020 08:39:56 +09:0010.84
1807/01/2020 19:58:12 +09:0007/01/2020 08:53:27 +09:0011.08
2007/01/2020 20:12:55 +09:0007/01/2020 08:21:24 +09:0011.86
2407/01/2020 12:00:00 +09:0007/01/2020 07:40:00 +09:004.33
2407/01/2020 17:00:00 +09:0007/01/2020 12:00:00 +09:005.00
2407/01/2020 21:10:00 +09:0007/01/2020 18:00:00 +09:003.17
2808/01/2020 18:00:00 +09:0008/01/2020 09:00:00 +09:009.00
2808/01/2020 14:05:19 +09:0008/01/2020 14:05:08 +09:000.00
3808/01/2020 18:00:55 +09:0008/01/2020 08:57:56 +09:009.05
2708/01/2020 18:04:45 +09:0008/01/2020 08:49:08 +09:009.26
4308/01/2020 18:22:03 +09:0008/01/2020 08:57:04 +09:009.42
2908/01/2020 18:26:04 +09:0008/01/2020 08:49:14 +09:009.61
2208/01/2020 18:46:32 +09:0008/01/2020 08:52:35 +09:009.90
2508/01/2020 19:06:44 +09:0008/01/2020 09:03:05 +09:0010.06

Hi @kbachova ,

 

We can use the following measure after create a axis table to meet your requriement:

 

Axis Table:

 

8.jpg

 

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
)

 

 

11.jpg12.jpg

 


If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.