Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

time duration between time frames

Hi all, I'm trying to calculate the duration of an event, but expressed as its parts falling between specific time frames. There are two tables, stops_table and hours_table: stops_table shows the ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi Anonymous 

    You could try this way as below:

    Step1:

    Add two columns in the stops_table as below:

    _maxhoursstart = CALCULATE(MAX(hours_table[hour start]),FILTER(hours_table,hours_table[hour start]<=stops_table[Stop start]))
    _minhoursend = CALCULATE(MIN(hours_table[hour end]),FILTER(hours_table,hours_table[hour end]>=stops_table[Stop end]))

    Step2:

    Then use this formula to create a measure

    Result = 
VAR _table =
    FILTER (
        CROSSJOIN ( stops_table, hours_table ),
        stops_table[_maxhoursstart] <= hours_table[hour start]
            && stops_table[_minhoursend] >= hours_table[hour end]
    )
RETURN
    SUMX (
        _table,
        IF (
            [Stop start] >= [hour start]
                && [Stop end] <= [hour end],
            DATEDIFF ( [Stop start], [Stop end], MINUTE ),
            IF (
                [Stop start] >= [hour start]
                    && [Stop end] > [hour end],
                DATEDIFF ( [Stop start], [hour end], MINUTE ),
                IF (
                    [Stop start] < [hour start]
                        && [Stop end] < [hour end],
                    DATEDIFF ( [hour start], [Stop end], MINUTE )
                )
            )
        )
    )

    Result:

    Regards,

    Lin

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi Anonymous 

You could try this way as below:

Step1:

Add two columns in the stops_table as below:

_maxhoursstart = CALCULATE(MAX(hours_table[hour start]),FILTER(hours_table,hours_table[hour start]<=stops_table[Stop start]))
_minhoursend = CALCULATE(MIN(hours_table[hour end]),FILTER(hours_table,hours_table[hour end]>=stops_table[Stop end]))

Step2:

Then use this formula to create a measure

Result = 
VAR _table =
    FILTER (
        CROSSJOIN ( stops_table, hours_table ),
        stops_table[_maxhoursstart] <= hours_table[hour start]
            && stops_table[_minhoursend] >= hours_table[hour end]
    )
RETURN
    SUMX (
        _table,
        IF (
            [Stop start] >= [hour start]
                && [Stop end] <= [hour end],
            DATEDIFF ( [Stop start], [Stop end], MINUTE ),
            IF (
                [Stop start] >= [hour start]
                    && [Stop end] > [hour end],
                DATEDIFF ( [Stop start], [hour end], MINUTE ),
                IF (
                    [Stop start] < [hour start]
                        && [Stop end] < [hour end],
                    DATEDIFF ( [hour start], [Stop end], MINUTE )
                )
            )
        )
    )

Result:

Regards,

Lin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    v-lili6-msft  ,
    thank you very much, following your advice I was able to make it work.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Sorry Lin but the solution is having problems. If an interruption started at 2:52 pm and ending at 5:30 pm, it should shows:

     

    8 min in the 2:30 - 3:00

    59 min in the 3:00 - 4:00

    59 min in the 4:00 - 5:00

    30 min in the 5:00 - 6:00

     

    but it only shows:

     

    8 min in the 2:30 - 3:00

    30 min in the 5:00 - 6:00

     

    please help

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous  ,

      you need to add addional conditions to handle the cases where the full hour falls between the events.
      Below you can find the logic I used to solve my problem.

       

      if [Stop start]>=[hour start] and [Stop start]<[hour end] and [Stop end]<=[hour end] then [Stop end]-[Stop start]
      else if [Stop start]>[hour start] and [Stop end]>[hour end] and [Stop start]<[hour end] then [hour end]-[Stop start]
      else if [Stop start]<[hour start] and [Stop end]<[hour end] and [Stop end]>[hour start] then [Stop end]-[hour start]
      else if [Stop start]<=[hour start] and [Stop start]<[hour end] and [Stop end]>[hour end] then [hour end]-[hour start]
      else if [Stop start]>[hour start] and [Stop start]>[hour end] and [hour end]>[Stop end] and [Stop end]>[hour start] then [Stop end]-[hour start]