cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

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

1 ACCEPTED SOLUTION
Community Support

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[_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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Support

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[_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

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

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

Frequent Visitor

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]`

Frequent Visitor

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors