cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
girinpanda Member
Member

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] * 8) - ([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] * 8) + ([End Time] - [9AM]) * 24,
                                                          9, [Business Days] * 8,
                                                        10, ([Business Days] * 8) + 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

Accepted Solutions
Community Support Team
Community Support Team

Re: Computing Time Metric (Difference of Two Time Values)

Hi @girinpanda,

 

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.
3 REPLIES 3
Community Support Team
Community Support Team

Re: Computing Time Metric (Difference of Two Time Values)

Hi @girinpanda,

 

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

Re: Computing Time Metric (Difference of Two Time Values)

Thanks, Dale! I'll try this out.

Community Support Team
Community Support Team

Re: Computing Time Metric (Difference of Two Time Values)

Hi @girinpanda,

 

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.