time duration between time frames

[Anonymous]

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 event start and end time, 

Stop start	Stop end
14:52	15:30
19:51	20:10
20:11	21:24

,while hours_table shows the time frames, expressed as hour start and hour end.

hour start	hour end
14:30	15:00
15:00	16:00
16:00	17:00
17:00	18:00
18:00	19:00
19:00	20:00
20:00	21:00
21:00	22:00

I need to calculate the duration in minutes, applying pretty much the following logic:

if stop start > hour start and stop end < hour end, then duration = stop end - stop start

if stop start > hour start and stop end > hour end, then duration = hour end - stop start

if stop start < hour start and stop end < hour ned, then duration = stop end - hour start
I am able to get there using DAX and utilizing FIRSTNONBLANK and LASTNONBLANK functions then filtering using the if statements.
The result is table like this:

hour start	hour end	stop start	stop end	stop duration
14:30	15:00	14:52	15:00	8
15:00	16:00	15:00	15:30	30
16:00	17:00
17:00	18:00
18:00	19:00
19:00	20:00	19:51	20:00	9
20:00	21:00	20:00	20:10	10
21:00	22:00	21:00	21:24	24

The problem is that, the above table is wrong, as there are cases where events with stop ends in a specific time frame and then another event starts in the same time frame, like this case:
In the time frame between 20:00 and 21:00, an event stopped at 20:10 (that started at 19:51), but another one started at 20:11 and lasted until 21:24. so the correct table visualization would be like this one:

hour start	hour end	stop i start	stop i end	stop n start	stop n end	stop duration
14:30	15:00	14:52	15:00			8
15:00	16:00	15:00	15:30			30
16:00	17:00
17:00	18:00
18:00	19:00
19:00	20:00	19:51	20:00			9
20:00	21:00	20:00	20:10	20:11	21:00	10+49=59
21:00	22:00			21:00	21:24	24

Any help appreciated

[v-lili6-msft · Accepted solution]

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]

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]

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]

[Anonymous]

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

[amitchandak]

If possible please share a sample pbix file after removing sensitive information.
Thanks