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: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
toxicshev
Frequent Visitor

Need help with overlapping duration only show values on the row with the minimum duration

‎03-11-2023 09:02 AM

Hi everyone, 

 

I've struggling with this one. Need help to write a measure or column to calculate a duration time. 
I have a table called TCensus with a column named "Admit Date & time", a column named "Discharge date & time", and column called " HourLogged Time", which is the differentce bewteen "Admit Date & time" and "Discharge date & time", there could be multiple rows that have the same Admit Date but with different admit time. I need to create a column or measure that only shows "" HourLogged Time" on the row where has the least HourLogged time on each Admit date. otherwise it will be 0.

see expected outcome below

toxicshev_1-1678553913387.png

 


For example, ""Admit Date & time" = Feb 1, there are 3 patient admitted at different time on Feb 1, the one in the red box has a duration(Hourlogged time) of 32 hours (differentce bewteen "Admit Date & time" and "Discharge date & time",) is overlapped with rest the 2 patients. I would like to only show 32 hours as the overlapped time on Feb 1. For other 2 patients on that day, showing 0. 

 

Your help is greatly appreciated!!

Thanks 

 

Labels:
Message 1 of 6
786 Views
0
5 REPLIES 5
andhiii079845
Solution Sage
Solution Sage

‎03-12-2023 10:21 AM

I think it is more tricky. I now calculated the count of ppl per datetime event (dicharge or admin, it is is not 100 % perfect now, because i look dirctly at the discharge and admin event and you have to decide the "<" or "<=")

andhiii079845_0-1678640421387.png

You see during the 1. Feb the count of ppl increase. The first persons leaving at 15:15 of 2. Feb. and the count of ppl decrease. Your mentioned overlapping time will be this time part. But the count of ppl is decreasing earlier.

andhiii079845_1-1678640822457.png

I also put in the 2. overlapping duration:

andhiii079845_6-1678641592941.png

I put your third overlapping duration in the diagram:

andhiii079845_5-1678641338161.png

I think you have to thinking twice about this logic. I see big interpretations problems right now.

Also the calculations logic is not clear enough. 

 

 

 







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 6 of 6
633 Views
0
andhiii079845
Solution Sage
Solution Sage

‎03-11-2023 11:42 AM

Thank you for your reply 🙂
It seems so that you want to calculated for a complete day the overlapping time. Is the count of people important when the overlapping time happenend? My logic will be do look during the day when the maximum of ppl for this day are reached and how long. I will try also to find out which person it is. Very interesting question 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 4 of 6
700 Views
0
toxicshev
Frequent Visitor
In response to andhiii079845

‎03-11-2023 02:26 PM

Regardless of how many people on the same day, all I want is to calculate the overlapping time by each single day.  Like highlighted in the red box blow, Admit date as Feb 1, the overlapping duration is 32 hours.

toxicshev_0-1678573532361.png

 

Message 5 of 6
664 Views
0
andhiii079845
Solution Sage
Solution Sage

‎03-11-2023 10:11 AM

Can you first please send a table (with the table function of the forum) with some data examples. I do not want to copy it manually. I think the logic in general with the overlapping time is difficult. Think about the following example, what is now the overlapping time? 

andhiii079845_0-1678557992441.png

What is if you have 7 persons and only 5 h overlapping time, in the rest of the time 6 person complete overlapping? 

andhiii079845_1-1678558161000.png

I want to say that you may have to think about the meaning of this overlapping time and the usage of it. 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 2 of 6
734 Views
0
toxicshev
Frequent Visitor
In response to andhiii079845

‎03-11-2023 11:07 AM

HI @andhiii079845 , 
Thanks for your fast reply, please see below my data table, 
To answer your question, in case of 7 people, only 5 are overlapped, then i just need to know the overlapping time of the 5.
I guess what i want to achieve is that to calculate the overlapping time if exists,  

toxicshev_0-1678561524491.png

 

toxicshev_1-1678561587157.png

 

IDAdmit DateAdmit Date & TimeDischarge DateDischarge Date & TimeAdmit TimeDischarge TimeHourLogged Time
11-Feb-222/1/2022 7:244-Feb-222/4/2022 13:307:24:32 AM1:30:00 PM78
21-Feb-222/1/2022 7:412-Feb-222/2/2022 16:307:41:07 AM4:30:00 PM33
31-Feb-222/1/2022 8:392-Feb-222/2/2022 16:008:39:19 AM4:00:00 PM32
42-Feb-222/2/2022 7:192-Feb-222/2/2022 15:157:19:33 AM3:15:00 PM8
52-Feb-222/2/2022 8:203-Feb-222/3/2022 10:458:20:12 AM10:45:00 AM26
62-Feb-222/2/2022 10:214-Feb-222/4/2022 12:5010:21:36 AM12:50:00 PM50
78-Feb-222/8/2022 6:558-Mar-223/8/2022 12:156:55:10 AM12:15:00 PM678
88-Feb-222/8/2022 7:1312-Feb-222/12/2022 9:307:13:30 AM9:30:00 AM98
99-Feb-222/9/2022 7:0412-Feb-222/12/2022 19:307:04:36 AM7:30:00 PM84
109-Feb-222/9/2022 7:4412-Feb-222/12/2022 16:457:44:42 AM4:45:00 PM81
119-Feb-222/9/2022 7:5110-Feb-222/10/2022 12:007:51:25 AM12:00:00 PM29
1210-Feb-222/10/2022 9:0913-Feb-222/13/2022 19:329:09:29 AM7:32:00 PM82
1311-Feb-222/11/2022 6:5116-Feb-222/16/2022 10:306:51:00 AM10:30:00 AM124
1412-Feb-222/12/2022 14:2819-Feb-222/19/2022 10:002:28:59 PM10:00:00 AM164
1514-Feb-222/14/2022 6:5119-Feb-222/19/2022 11:456:51:43 AM11:45:00 AM125
1614-Feb-222/14/2022 8:4415-Feb-222/15/2022 16:308:44:31 AM4:30:00 PM32
1715-Feb-222/15/2022 12:0219-Feb-222/19/2022 13:3012:02:57 PM1:30:00 PM97
1816-Feb-222/16/2022 6:3217-Feb-222/17/2022 17:506:32:03 AM5:50:00 PM35
1916-Feb-222/16/2022 8:0918-Feb-222/18/2022 17:508:09:08 AM5:50:00 PM57
2017-Feb-222/17/2022 7:5721-Feb-222/21/2022 14:107:57:58 AM2:10:00 PM103
2117-Feb-222/17/2022 8:0617-Feb-222/17/2022 12:008:06:09 AM12:00:00 PM4
2218-Feb-222/18/2022 7:3419-Feb-222/19/2022 18:007:34:19 AM6:00:00 PM35
2321-Feb-222/21/2022 8:4222-Feb-222/22/2022 13:008:42:47 AM1:00:00 PM29
2421-Feb-222/21/2022 9:0022-Feb-222/22/2022 14:159:00:04 AM2:15:00 PM29
2521-Feb-222/21/2022 11:2622-Feb-222/22/2022 9:4011:26:21 AM9:40:00 AM22
2622-Feb-222/22/2022 7:0926-Feb-222/26/2022 11:007:09:52 AM11:00:00 AM100
2722-Feb-222/22/2022 9:4623-Feb-222/23/2022 19:059:46:56 AM7:05:00 PM34
2823-Feb-222/23/2022 6:4426-Feb-222/26/2022 14:006:44:48 AM2:00:00 PM80
2923-Feb-222/23/2022 8:5125-Feb-222/25/2022 17:108:51:43 AM5:10:00 PM57
3024-Feb-222/24/2022 6:5528-Feb-222/28/2022 13:156:55:30 AM1:15:00 PM103
3124-Feb-222/24/2022 10:1627-Feb-222/27/2022 11:3010:16:07 AM11:30:00 AM73
3225-Feb-222/25/2022 6:567-Mar-223/7/2022 6:306:56:51 AM6:30:00 AM240
3325-Feb-222/25/2022 7:2025-Feb-222/25/2022 18:207:20:10 AM6:20:00 PM11
Message 3 of 6
717 Views

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All
Top Solution Authors
View All