Solved: Re: Help in finding concept how to calculate total...

kbachova · ‎01-23-2020

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!

v-lid-msft · ‎01-23-2020

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,

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

kbachova · ‎01-23-2020

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:

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

rajulshah · ‎01-23-2020

Hello @kbachova,

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

amitchandak · ‎01-23-2020

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...

!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

kbachova · ‎01-23-2020

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

v-lid-msft · ‎01-23-2020

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,

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.

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

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly