March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
hi @sstefan9
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
hi @sstefan9
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
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
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]
@v-lili6-msft ,
thank you very much, following your advice I was able to make it work.
If possible please share a sample pbix file after removing sensitive information.
Thanks
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
148 | |
92 | |
72 | |
58 |