Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
brisacisnerosc
New Member

How can I calculate elapsed working time within a date filter in Power BI?

Tuesday

Hi everyone,

I’m trying to calculate the elapsed duration of activities within a selected date range in Power BI. I have two columns:

  • Start Time (DateTime)

  • End Time (DateTime), which can be blank if the activity is still ongoing.

I want to create a measure that calculates the working hours only within the range defined by my date filter. For example, if an activity starts on July 8 at 2:00 PM and ends on July 15 at 9:00 AM, but my report is filtered from July 9 to July 11, I only want to count the working hours between those two dates.

The calculation should follow these rules:

  • Only count time between 7:00 AM and 4:30 PM (working hours).

  • Subtract 1 hour for lunch (12:00 PM to 1:00 PM) if the activity overlaps that time.

  • Exclude weekends (Saturday and Sunday).

My extra question is:
I’m not sure which column to use for the date filter so that the measure works correctly. Should it be based on Start Time, End Time, or maybe a disconnected calendar table? I want the measure to respond accurately to the date range I select on my report.

Any help or best practices would be greatly appreciated!

Thanks in advance!

Message 1 of 7
101 Views
0
6 REPLIES 6
v-venuppu
Community Support
Community Support

9 hours ago

Hi @brisacisnerosc ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @techies @FBergamaschi for the prompt response.

I have implemented the working time logic considering working hours (07:00–16:30), excluding weekends, and deducting a 1-hour lunch break (12:00–13:00). The output dynamically responds to the date slicer and correctly shows blank where no activity overlaps the date.

 

Please find the attached PBIX file for your reference.

Thank you.

 

Elapsed working time.pbix
Message 7 of 7
13 Views
0
techies
Super User
Super User

Tuesday

Hi @brisacisnerosc pls share the sample datasets

 

 

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978
Message 5 of 7
86 Views
0
brisacisnerosc
New Member
In response to techies

Tuesday

 

ID ACTIVITYSTART TIMEEND TIME
4e0c48e401/07/2025 07:2102/07/2025 09:05
24069dc101/07/2025 07:2201/07/2025 16:17
792309eb01/07/2025 07:2801/07/2025 16:14
1ac89f1f01/07/2025 07:3001/07/2025 16:10
7d6b3aaa01/07/2025 07:3201/07/2025 14:29
8f54026101/07/2025 07:5002/07/2025 09:19
95b767f601/07/2025 10:0602/07/2025 09:47
712a5dbf01/07/2025 14:2901/07/2025 16:18
ecd6a75101/07/2025 16:1802/07/2025 08:36
109d5abd02/07/2025 07:0202/07/2025 08:27
670de4f502/07/2025 07:0202/07/2025 08:37
d70f066a02/07/2025 07:4002/07/2025 16:19
15b5932902/07/2025 09:1402/07/2025 09:18
a4cafcf702/07/2025 09:4802/07/2025 11:01
bbaa150e02/07/2025 10:0608/07/2025 09:16
bfe89f0c02/07/2025 10:0802/07/2025 10:18
bd8880f602/07/2025 11:0202/07/2025 13:03
5abd642902/07/2025 13:0502/07/2025 16:13
3b524ce202/07/2025 13:1002/07/2025 13:19
dd6cdf5403/07/2025 07:0403/07/2025 16:15
9c3fdac703/07/2025 07:0503/07/2025 16:16
0169c67703/07/2025 07:1703/07/2025 16:18
27b6020703/07/2025 07:1904/07/2025 16:00
5739bbcb03/07/2025 07:2008/07/2025 09:15
1fff1c9b03/07/2025 07:2303/07/2025 16:14
515a129903/07/2025 08:0503/07/2025 16:14
f92da84003/07/2025 08:0707/07/2025 07:37
8008d04603/07/2025 14:0804/07/2025 09:16
3f34540304/07/2025 07:4904/07/2025 16:22
934444f304/07/2025 09:4204/07/2025 09:43
aabaa29f04/07/2025 09:5204/07/2025 11:02
a95e6d9304/07/2025 10:3304/07/2025 16:15
10b08ee007/07/2025 07:1607/07/2025 08:41
6ec4387c07/07/2025 08:0907/07/2025 13:07
dbc5987207/07/2025 08:3308/07/2025 07:59
c1e1ea2f07/07/2025 08:4107/07/2025 09:00
f2518d6207/07/2025 09:4308/07/2025 09:29
7615c22f07/07/2025 13:0707/07/2025 14:44
07b6850a07/07/2025 14:4408/07/2025 07:12
e83e78f607/07/2025 15:5407/07/2025 16:11
ba7b18ca08/07/2025 07:0408/07/2025 08:53
257ecfd408/07/2025 07:1008/07/2025 07:11
6c6c86b208/07/2025 07:1008/07/2025 09:18
e4bda07908/07/2025 07:1208/07/2025 10:02
d023633d08/07/2025 07:5908/07/2025 14:44
7529231a08/07/2025 09:3008/07/2025 16:29
d78511db08/07/2025 10:0208/07/2025 16:01
5500fa3508/07/2025 13:5310/07/2025 14:53
b591a94008/07/2025 14:4409/07/2025 10:15
cb07ac8809/07/2025 07:0909/07/2025 07:58
dd7958c809/07/2025 07:0909/07/2025 08:48
6febf55b09/07/2025 07:1109/07/2025 16:20
51a5c4ac09/07/2025 07:5809/07/2025 08:18
404a5c6309/07/2025 08:1809/07/2025 08:34
c481894609/07/2025 08:3409/07/2025 09:44
c6f50ea209/07/2025 08:4809/07/2025 14:39
de23c40509/07/2025 10:2110/07/2025 10:35
d028d2c809/07/2025 10:4109/07/2025 11:08
0006d66a09/07/2025 10:4709/07/2025 10:54
2e8bcd9609/07/2025 10:5409/07/2025 16:20
a97055af09/07/2025 11:1009/07/2025 13:09
8543d59809/07/2025 13:1109/07/2025 15:28
fd040cad09/07/2025 14:3909/07/2025 15:56
1cd1ba4109/07/2025 15:4011/07/2025 07:56
4adbaf8510/07/2025 07:1310/07/2025 09:29
a1fc6ced10/07/2025 07:3310/07/2025 10:30
7adb034410/07/2025 07:3410/07/2025 11:23
f03e7d7110/07/2025 07:4510/07/2025 07:46
dc60edb610/07/2025 07:4610/07/2025 07:46
f3f0f16310/07/2025 08:3010/07/2025 10:41
1d294d3b10/07/2025 09:2910/07/2025 11:05
c16541a910/07/2025 10:3011/07/2025 07:22
f364270510/07/2025 10:4110/07/2025 13:26
0e1b963010/07/2025 11:0510/07/2025 11:18
e5e621db10/07/2025 11:1310/07/2025 16:22
04acf5b010/07/2025 11:1810/07/2025 11:43
130eaf9110/07/2025 11:4310/07/2025 13:23
91e6c8b010/07/2025 13:2310/07/2025 14:06
de11d24010/07/2025 13:2611/07/2025 09:05
afc0b5aa10/07/2025 14:5311/07/2025 15:01
d82163de11/07/2025 07:2611/07/2025 08:57
23b0783111/07/2025 08:0214/07/2025 10:21
f55e0aa311/07/2025 07:3011/07/2025 09:00
1b57370b11/07/2025 09:1911/07/2025 10:40
6c2633ac11/07/2025 09:4614/07/2025 10:03
0e40d10211/07/2025 10:1911/07/2025 16:03
aaac0df411/07/2025 10:4011/07/2025 16:22
00f6501d11/07/2025 14:3514/07/2025 10:04
77b65cc414/07/2025 07:4014/07/2025 07:47
6bba657614/07/2025 07:5115/07/2025 07:12
ec2c1c9514/07/2025 07:5714/07/2025 16:25
966994d914/07/2025 08:06 
61767af714/07/2025 07:0014/07/2025 08:37
0b523fa814/07/2025 08:4014/07/2025 14:25
4dab903414/07/2025 08:5314/07/2025 09:25
e4a87ec914/07/2025 10:2514/07/2025 16:04
232dfba114/07/2025 10:5614/07/2025 16:04
c3d5af5614/07/2025 13:2714/07/2025 16:27
e8d815d114/07/2025 13:5515/07/2025 07:30
b808b45214/07/2025 14:1414/07/2025 16:29
f3c7efae14/07/2025 14:5415/07/2025 07:04
9718669414/07/2025 21:27 
c4842be115/07/2025 07:01 
f899cc0915/07/2025 07:0415/07/2025 10:30
096ff29215/07/2025 07:05 
47585dc115/07/2025 07:06 
d62fd73c15/07/2025 07:07 
6338c4b015/07/2025 07:0815/07/2025 13:05
a60d437615/07/2025 07:13 
e5b0d15915/07/2025 07:30 
b174bacc15/07/2025 08:07 
Message 6 of 7
70 Views
0
FBergamaschi
Post Prodigy
Post Prodigy

Tuesday

Please provide a few rows of the activities table with start and end date/time

 

On your question, I am not sure if you are asking what relationship to estabilish between a calendar table and the activities table (model question) or if you are asking how to filter data via DAX (measure question)

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Message 2 of 7
96 Views
0
brisacisnerosc
New Member
In response to FBergamaschi

Tuesday

Regarding your question — I think it's a bit of both:

  • From a data model perspective, I’m wondering how best to structure the relationship between a calendar table and the activities table, especially since activities can span multiple days.

  • From a DAX perspective, I’d like to know how to calculate working hours correctly within the selected date range in the report.

Thanks again for the support!

— Brisa

ID ACTIVITYSTART TIMEEND TIME
4e0c48e401/07/2025 07:2102/07/2025 09:05
24069dc101/07/2025 07:2201/07/2025 16:17
792309eb01/07/2025 07:2801/07/2025 16:14
1ac89f1f01/07/2025 07:3001/07/2025 16:10
7d6b3aaa01/07/2025 07:3201/07/2025 14:29
8f54026101/07/2025 07:5002/07/2025 09:19
95b767f601/07/2025 10:0602/07/2025 09:47
712a5dbf01/07/2025 14:2901/07/2025 16:18
ecd6a75101/07/2025 16:1802/07/2025 08:36
109d5abd02/07/2025 07:0202/07/2025 08:27
670de4f502/07/2025 07:0202/07/2025 08:37
d70f066a02/07/2025 07:4002/07/2025 16:19
15b5932902/07/2025 09:1402/07/2025 09:18
a4cafcf702/07/2025 09:4802/07/2025 11:01
bbaa150e02/07/2025 10:0608/07/2025 09:16
bfe89f0c02/07/2025 10:0802/07/2025 10:18
bd8880f602/07/2025 11:0202/07/2025 13:03
5abd642902/07/2025 13:0502/07/2025 16:13
3b524ce202/07/2025 13:1002/07/2025 13:19
dd6cdf5403/07/2025 07:0403/07/2025 16:15
9c3fdac703/07/2025 07:0503/07/2025 16:16
0169c67703/07/2025 07:1703/07/2025 16:18
27b6020703/07/2025 07:1904/07/2025 16:00
5739bbcb03/07/2025 07:2008/07/2025 09:15
1fff1c9b03/07/2025 07:2303/07/2025 16:14
515a129903/07/2025 08:0503/07/2025 16:14
f92da84003/07/2025 08:0707/07/2025 07:37
8008d04603/07/2025 14:0804/07/2025 09:16
3f34540304/07/2025 07:4904/07/2025 16:22
934444f304/07/2025 09:4204/07/2025 09:43
aabaa29f04/07/2025 09:5204/07/2025 11:02
a95e6d9304/07/2025 10:3304/07/2025 16:15
10b08ee007/07/2025 07:1607/07/2025 08:41
6ec4387c07/07/2025 08:0907/07/2025 13:07
dbc5987207/07/2025 08:3308/07/2025 07:59
c1e1ea2f07/07/2025 08:4107/07/2025 09:00
f2518d6207/07/2025 09:4308/07/2025 09:29
7615c22f07/07/2025 13:0707/07/2025 14:44
07b6850a07/07/2025 14:4408/07/2025 07:12
e83e78f607/07/2025 15:5407/07/2025 16:11
ba7b18ca08/07/2025 07:0408/07/2025 08:53
257ecfd408/07/2025 07:1008/07/2025 07:11
6c6c86b208/07/2025 07:1008/07/2025 09:18
e4bda07908/07/2025 07:1208/07/2025 10:02
d023633d08/07/2025 07:5908/07/2025 14:44
7529231a08/07/2025 09:3008/07/2025 16:29
d78511db08/07/2025 10:0208/07/2025 16:01
5500fa3508/07/2025 13:5310/07/2025 14:53
b591a94008/07/2025 14:4409/07/2025 10:15
cb07ac8809/07/2025 07:0909/07/2025 07:58
dd7958c809/07/2025 07:0909/07/2025 08:48
6febf55b09/07/2025 07:1109/07/2025 16:20
51a5c4ac09/07/2025 07:5809/07/2025 08:18
404a5c6309/07/2025 08:1809/07/2025 08:34
c481894609/07/2025 08:3409/07/2025 09:44
c6f50ea209/07/2025 08:4809/07/2025 14:39
de23c40509/07/2025 10:2110/07/2025 10:35
d028d2c809/07/2025 10:4109/07/2025 11:08
0006d66a09/07/2025 10:4709/07/2025 10:54
2e8bcd9609/07/2025 10:5409/07/2025 16:20
a97055af09/07/2025 11:1009/07/2025 13:09
8543d59809/07/2025 13:1109/07/2025 15:28
fd040cad09/07/2025 14:3909/07/2025 15:56
1cd1ba4109/07/2025 15:4011/07/2025 07:56
4adbaf8510/07/2025 07:1310/07/2025 09:29
a1fc6ced10/07/2025 07:3310/07/2025 10:30
7adb034410/07/2025 07:3410/07/2025 11:23
f03e7d7110/07/2025 07:4510/07/2025 07:46
dc60edb610/07/2025 07:4610/07/2025 07:46
f3f0f16310/07/2025 08:3010/07/2025 10:41
1d294d3b10/07/2025 09:2910/07/2025 11:05
c16541a910/07/2025 10:3011/07/2025 07:22
f364270510/07/2025 10:4110/07/2025 13:26
0e1b963010/07/2025 11:0510/07/2025 11:18
e5e621db10/07/2025 11:1310/07/2025 16:22
04acf5b010/07/2025 11:1810/07/2025 11:43
130eaf9110/07/2025 11:4310/07/2025 13:23
91e6c8b010/07/2025 13:2310/07/2025 14:06
de11d24010/07/2025 13:2611/07/2025 09:05
afc0b5aa10/07/2025 14:5311/07/2025 15:01
d82163de11/07/2025 07:2611/07/2025 08:57
23b0783111/07/2025 08:0214/07/2025 10:21
f55e0aa311/07/2025 07:3011/07/2025 09:00
1b57370b11/07/2025 09:1911/07/2025 10:40
6c2633ac11/07/2025 09:4614/07/2025 10:03
0e40d10211/07/2025 10:1911/07/2025 16:03
aaac0df411/07/2025 10:4011/07/2025 16:22
00f6501d11/07/2025 14:3514/07/2025 10:04
77b65cc414/07/2025 07:4014/07/2025 07:47
6bba657614/07/2025 07:5115/07/2025 07:12
ec2c1c9514/07/2025 07:5714/07/2025 16:25
966994d914/07/2025 08:06 
61767af714/07/2025 07:0014/07/2025 08:37
0b523fa814/07/2025 08:4014/07/2025 14:25
4dab903414/07/2025 08:5314/07/2025 09:25
e4a87ec914/07/2025 10:2514/07/2025 16:04
232dfba114/07/2025 10:5614/07/2025 16:04
c3d5af5614/07/2025 13:2714/07/2025 16:27
e8d815d114/07/2025 13:5515/07/2025 07:30
b808b45214/07/2025 14:1414/07/2025 16:29
f3c7efae14/07/2025 14:5415/07/2025 07:04
9718669414/07/2025 21:27 
c4842be115/07/2025 07:01 
f899cc0915/07/2025 07:0415/07/2025 10:30
096ff29215/07/2025 07:05 
47585dc115/07/2025 07:06 
d62fd73c15/07/2025 07:07 
6338c4b015/07/2025 07:0815/07/2025 13:05
a60d437615/07/2025 07:13 
e5b0d15915/07/2025 07:30 
b174bacc15/07/2025 08:07 
Message 4 of 7
61 Views
0
brisacisnerosc
New Member
In response to FBergamaschi

Tuesday

Regarding your question — I think it's a bit of both:

  • From a data model perspective, I’m wondering how best to structure the relationship between a calendar table and the activities table, especially since activities can span multiple days.

  • From a DAX perspective, I’d like to know how to calculate working hours correctly within the selected date range in the report.

  • The file includes the columns 'Start Time' and 'End Time', with a few sample rows showing ongoing and completed activities.

  • ID ACTIVITYSTART TIMEEND TIME
    fd040cad09/07/2025 14:3909/07/2025 15:56
    1cd1ba4109/07/2025 15:4011/07/2025 07:56
    4adbaf8510/07/2025 07:1310/07/2025 09:29
    a1fc6ced10/07/2025 07:3310/07/2025 10:30
    7adb034410/07/2025 07:3410/07/2025 11:23
    f03e7d7110/07/2025 07:4510/07/2025 07:46
    dc60edb610/07/2025 07:4610/07/2025 07:46
    f3f0f16310/07/2025 08:3010/07/2025 10:41
    1d294d3b10/07/2025 09:2910/07/2025 11:05
    c16541a910/07/2025 10:3011/07/2025 07:22
    f364270510/07/2025 10:4110/07/2025 13:26
    0e1b963010/07/2025 11:0510/07/2025 11:18
    e5e621db10/07/2025 11:1310/07/2025 16:22
    04acf5b010/07/2025 11:1810/07/2025 11:43
    130eaf9110/07/2025 11:4310/07/2025 13:23
    91e6c8b010/07/2025 13:2310/07/2025 14:06
    de11d24010/07/2025 13:2611/07/2025 09:05
    afc0b5aa10/07/2025 14:5311/07/2025 15:01
    d82163de11/07/2025 07:2611/07/2025 08:57
    23b0783111/07/2025 08:0214/07/2025 10:21
    f55e0aa311/07/2025 07:3011/07/2025 09:00
    1b57370b11/07/2025 09:1911/07/2025 10:40
    6c2633ac11/07/2025 09:4614/07/2025 10:03
    0e40d10211/07/2025 10:1911/07/2025 16:03
    aaac0df411/07/2025 10:4011/07/2025 16:22
    00f6501d11/07/2025 14:3514/07/2025 10:04
    77b65cc414/07/2025 07:4014/07/2025 07:47
    6bba657614/07/2025 07:5115/07/2025 07:12
    ec2c1c9514/07/2025 07:5714/07/2025 16:25
    966994d914/07/2025 08:06 
    61767af714/07/2025 07:0014/07/2025 08:37
    0b523fa814/07/2025 08:4014/07/2025 14:25
    4dab903414/07/2025 08:5314/07/2025 09:25
    e4a87ec914/07/2025 10:2514/07/2025 16:04
    232dfba114/07/2025 10:5614/07/2025 16:04
    c3d5af5614/07/2025 13:2714/07/2025 16:27
    e8d815d114/07/2025 13:5515/07/2025 07:30
    b808b45214/07/2025 14:1414/07/2025 16:29
    f3c7efae14/07/2025 14:5415/07/2025 07:04
    9718669414/07/2025 21:27 
    c4842be115/07/2025 07:01 
    f899cc0915/07/2025 07:0415/07/2025 10:30
    096ff29215/07/2025 07:05 
    47585dc115/07/2025 07:06 
    d62fd73c15/07/2025 07:07 
    6338c4b015/07/2025 07:08 
    a60d437615/07/2025 07:13 
    e5b0d15915/07/2025 07:30 
    b174bacc15/07/2025 08:07 

Thanks again for the support!

— Brisa

Message 3 of 7
62 Views
0

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All