Calculate free time

[saanah2019]

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.

date

start

end

datetime

duration

daily appoint allowed

total daily duration

free time(daily - total daily duration)

1/1/2018

10:00

10:30

1/1/2018 10:00

30

480

115

365

1/1/2018

10:40

11:00

1/1/2018 10:30

30

1/1/2018

11:05

12

1/1/2018 11:05

55

1/4/2018

11:00

11:30

1/4/2018 11:00

30

480

90

390

1/4/2018

11:40

12:40

1/4/2018 11:40

60

1/6/2018

11:00

12

1/6/2018 11:00

60

480

60

420

1/7/2018

11:00

11:50

1/7/2018 11:00

50

480

150

1/7/2018

12:00

12:20

1/7/2018 12:00

20

1/7/2018

12:30

1:00

1/7/2018 12:30

30

1/7/2018

13:05

13:55

1/7/2018  13:05:00 PM

50

So I have all the columns except for the last three and want to know how to achieve them in Power BI. Thank you. 

[saanah2019]

@v-piga-msft just a quick add. what happens when there is another column and I also need to take that into consideraton.So now I would want to basically look at the names, if they are the same then look at the date, and then for all the same date sum the duration

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

[v-piga-msft · Accepted solution]

Hi @saanah2019 ,

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