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
Anonymous
Not applicable

Computing Time Metric (Difference of Two Time Values)

I'm trying to compute a Time Metric (measured in hours) which is the difference of two datetimes. I can't just use DATEDIFF since I have the following assumptions:

                    1. Only the hours which falls within 9AM to 5PM will be recorded in Time Metric

                    2. Only the hours during the WEEKDAYS will be recorded in Time Metric

 

Here's my computation for the Time Metric:

       

     Time Metric = SWITCH([Shift], 1, [Business Days] * 8 + ([End Time] - [Start Time]) * 24,
                                                          2, ([Business Days] * 😎 - ([Start Time] - [End Time]) * 24,
                                                          3, [Business Days] * 8 + ([5PM] - [Start Time]) * 24,
                                                          4, ([Business Days] - 1) * 8 + ([5PM] - [Start Time]) * 24,
                                                          5, ([Business Days] - 1) * 8 + ([End Time] - [9AM]) * 24,
                                                          6, [Business Days] * 8,
                                                          7, ([Business Days] - 1) * 8,
                                                          8, ([Business Days] * 😎 + ([End Time] - [9AM]) * 24,
                                                          9, [Business Days] * 8,
                                                        10, ([Business Days] * 😎 + 8
                                                )

                                where [Business Days] is the number of weekdays within two timestamps 

                                & [9AM] and [5PM] are TIMEVALUES for "9:00:00AM" and "5:00:00PM" respectively

 

        and the computation for [SHIFT] is:

 

      Shift = IF(([Start Time] >= [9AM] && [Start Time] <= [5PM]) && ([End Time] >= [9AM]

                       && [End Time] <= [5PM]) && ([Start Time] < [End Time]), 1,
                         IF(([Start Time] >= [9AM] && [Start Time] <= [5PM])  && ([End Time] >= [9AM]

                               && [End Time] <= [5PM]) && ([Start Time] > [End Time]), 2,
                        IF(([Start Time] >= [9AM] && [Start Time] <= [5PM]) && ([End Time] > [5PM]), 3,
                        IF(([Start Time] >= [9AM] && [Start Time] <= [5PM]) && ([End Time] < [9AM]), 4,
                        IF(([Start Time] > [5PM]) && ([End Time] >= [9AM] && [End Time] <= [5PM]), 5,
                        IF([Start Time] > [5PM] && [End Time] > [5PM], 6,
                        IF([Start Time] > [5PM] && [End Time] < [9AM], 7,
                        IF([Start Time] < [9AM] && [End Time] >= [9AM] && [End Time] <= [5PM], 8,
                        IF([Start Time] < [9AM] && [End Time] < [9AM], 9,
                        IF([Start Time] < [9AM] && [End Time] > [5PM], 10
                       ))))))))))

 

This formula is already working and returns the correct hours. But is there a way to optimize this formula? Any tips or help would be greatly appreciated! 

 

Thanks! Smiley Very Happy

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

This won't be easy. Please check another solution below. You also can check it step by step in this file.

AllInOne =
VAR standardStart =
    IF (
        TIMEVALUE ( [Start Time] ) < TIME ( 9, 0, 0 ),
        DATEVALUE ( [Start Time] ) + TIME ( 9, 0, 0 ),
        IF (
            TIMEVALUE ( [Start Time] ) > TIME ( 17, 0, 0 ),
            DATEVALUE ( [Start Time] ) + TIME ( 17, 0, 0 ),
            [Start Time]
        )
    )
VAR standardEnd =
    IF (
        TIMEVALUE ( [End Time] ) < TIME ( 9, 0, 0 ),
        DATEVALUE ( [End Time] ) + TIME ( 9, 0, 0 ),
        IF (
            TIMEVALUE ( [End Time] ) > TIME ( 17, 0, 0 ),
            DATEVALUE ( [End Time] ) + TIME ( 17, 0, 0 ),
            [End Time]
        )
    )
VAR businessDay =
    CALCULATE (
        SUMX ( DateTable, [IsWorkday] ),
        FILTER (
            'DateTable',
            'DateTable'[Date] > [Start Time].[Date]
                && 'DateTable'[Date] < [End Time].[Date]
        )
    )
RETURN
    IF (
        [Start Time] > [End Time],
        9999,
        IF (
            [Start Time].[Date] = [End Time].[Date],
            DATEDIFF ( standardstart, standardend, HOUR ),
            [Business Days] * 8
                + DATEDIFF ( TIMEVALUE ( standardstart ), TIME ( 17, 0, 0 ), HOUR )
                + DATEDIFF ( TIME ( 9, 0, 0 ), TIMEVALUE ( standardend ), HOUR )
        )
    )

Note: 9999 means Error. A date table is needed.

Computing_Time_Metric_Difference_of_Two_Time_Values

 

Best Regards,

Dale

 

Community Support Team _ Dale
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

3 REPLIES 3
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

Could you please mark the proper answer as solution?

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

This won't be easy. Please check another solution below. You also can check it step by step in this file.

AllInOne =
VAR standardStart =
    IF (
        TIMEVALUE ( [Start Time] ) < TIME ( 9, 0, 0 ),
        DATEVALUE ( [Start Time] ) + TIME ( 9, 0, 0 ),
        IF (
            TIMEVALUE ( [Start Time] ) > TIME ( 17, 0, 0 ),
            DATEVALUE ( [Start Time] ) + TIME ( 17, 0, 0 ),
            [Start Time]
        )
    )
VAR standardEnd =
    IF (
        TIMEVALUE ( [End Time] ) < TIME ( 9, 0, 0 ),
        DATEVALUE ( [End Time] ) + TIME ( 9, 0, 0 ),
        IF (
            TIMEVALUE ( [End Time] ) > TIME ( 17, 0, 0 ),
            DATEVALUE ( [End Time] ) + TIME ( 17, 0, 0 ),
            [End Time]
        )
    )
VAR businessDay =
    CALCULATE (
        SUMX ( DateTable, [IsWorkday] ),
        FILTER (
            'DateTable',
            'DateTable'[Date] > [Start Time].[Date]
                && 'DateTable'[Date] < [End Time].[Date]
        )
    )
RETURN
    IF (
        [Start Time] > [End Time],
        9999,
        IF (
            [Start Time].[Date] = [End Time].[Date],
            DATEDIFF ( standardstart, standardend, HOUR ),
            [Business Days] * 8
                + DATEDIFF ( TIMEVALUE ( standardstart ), TIME ( 17, 0, 0 ), HOUR )
                + DATEDIFF ( TIME ( 9, 0, 0 ), TIMEVALUE ( standardend ), HOUR )
        )
    )

Note: 9999 means Error. A date table is needed.

Computing_Time_Metric_Difference_of_Two_Time_Values

 

Best Regards,

Dale

 

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

Thanks, Dale! I'll try this out.

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.