Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
@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 |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |