Calculate free time

Question

Hi, I have an appointment table and I want to find out the times between appointments and the time there is no appointment. So I have date, datetime, starttime, endtime, duration and I want to declare a total time daily of allowable appointments of 8 hours. I think the easiest way to achieve this is to create a new column and then have it look at each day, add the duration and subtract this sum from the 8 hours and the end result would be my time between appointments.&nbsp;datestartend&nbsp;datetimeduration&nbsp;daily appoint allowed&nbsp;total daily duration&nbsp;free time(daily - total daily duration)1/1/201810:0010:301/1/2018 10:0030&nbsp;480&nbsp;115&nbsp;&nbsp;365&nbsp;&nbsp;1/1/201810:4011:001/1/2018 10:3030&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/1/201811:05121/1/2018 11:0555&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/4/201811:0011:301/4/2018 11:0030&nbsp;480&nbsp;90&nbsp;&nbsp;390&nbsp;&nbsp;1/4/201811:4012:401/4/2018 11:4060&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/6/201811:00121/6/2018 11:0060&nbsp;480&nbsp;60&nbsp;&nbsp;420&nbsp;&nbsp;1/7/201811:0011:501/7/2018 11:0050&nbsp;480&nbsp;150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/7/201812:0012:201/7/2018 12:0020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/7/201812:301:001/7/2018 12:3030&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/7/201813:0513:551/7/2018&nbsp; 13:05:00 PM50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;So I have all the columns except for the last three and want to know how to achieve them in Power BI. Thank you.&nbsp;

v-piga-msft · Accepted Answer

Hi&nbsp;saanah2019&nbsp;,
Do you want to get the output below?

If so, please create the calculated columns with the formulas below.
daily appoint allowed = 8*60
total daily duration = CALCULATE(SUM(Table1[duration]),ALLEXCEPT(Table1,'Table1'[date]))
free time = 'Table1'[daily appoint allowed]-'Table1'[total daily duration]
If you still need help, please feel free to ask.
Best Regards,
Cherry
&nbsp;

Forum Discussion

Calculate free time

2 Replies

Related content

calculating time

Calculate Time

Is Fabric (Free) license time limited?

Calculating day/time difference

Wrong Time Calculation



name	date	start	end	datetime	duration	daily appoint allowed	total daily duration	free time(daily - total daily duration)
sam	1/1/2018	10:00	10:30	1/1/2018 10:00	30	480	60		420
sam	1/1/2018	10:40	11:00	1/1/2018 10:30	30		60		420
kate	1/1/2018	11:05	12	1/1/2018 11:05	55	480	55		425
sam	1/4/2018	11:00	11:30	1/4/2018 11:00	30	480	90		390
sam	1/4/2018	11:40	12:40	1/4/2018 11:40	60		90		390
kate	1/6/2018	11:00	12	1/6/2018 11:00	60	480	60		420
kate	1/7/2018	11:00	11:50	1/7/2018 11:00	50	480	70		410
kate	1/7/2018	12:00	12:20	1/7/2018 12:00	20		70		410
sam	1/7/2018	12:30	1:00	1/7/2018 12:30	30	480	80		400
sam	1/7/2018	13:05	13:55	1/7/2018 13:05:00 PM	50		80		400