Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sstefan9
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 startStop end
14:5215:30
19:5120:10
20:1121:24

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

hour starthour end
14:3015:00
15:0016:00
16:0017:00
17:0018:00
18:0019:00
19:0020:00
20:0021:00
21:0022: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 starthour endstop startstop endstop duration
14:3015:0014:5215:008
15:0016:0015:0015:3030
16:0017:00   
17:0018:00   
18:0019:00   
19:0020:0019:5120:009
20:0021:0020:0020:1010
21:0022:0021:0021:2424


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 starthour endstop i startstop i endstop n startstop n endstop duration
14:3015:0014:5215:00  8
15:0016:0015:0015:30  30
16:0017:00     
17:0018:00     
18:0019:00     
19:0020:0019:5120:00  9
20:0021:0020:0020:1020:1121:0010+49=59
21:0022:00  21:0021:2424


Any help appreciated

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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:

1.JPG

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.

View solution in original post

5 REPLIES 5
v-lili6-msft
Community Support
Community Support

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:

1.JPG

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

 

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.

 

amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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