Building a Time-Based Calendar in Power Apps
By Craig White
In the past two articles, we’ve built a very simple menu component and a weekly calendar. For every date selected from the calendar, the wife now needs the app to show scheduled appointments for that day and any subsequent available time. Should be easy, right?
We were using Microsoft Teams as inspiration again; I thought, or hoped that the wife would want something like this:
I’m sitting there, literally building this view in my head.
“Flexible height gallery with dates, bring the appointments table in and put in a nested gallery, filter nested gallery where date equals ThisItem.Date. Easy“.
I even mocked it up to prepare for this article, it took me less than 5 minutes to POC it. We’re not talking Kristine Kolodziejski-level design at this stage, but purely for a functional model:
But no, the wife doesn’t want that, she wants the more traditional view with times down the side:
A bit more of a head scratcher; plotting the appointments shouldn’t be too hard, but the size of the appointment blocks will be dictated by duration. Also, how do you even ‘do’ a time-based calendar? Whilst considering that, I was pondering the other requirements:
> Needs to be in 15 minute time blocks from 08:00-20:00; these 15 minute blocks will allow for buffer time between one treatment and the next, in case one overruns. 08:00-20:00 is the range for opening hours.
> Match times to existing appointments; if a 15 minute block is taken, block it out visually to indicate something is scheduled.
> Styling for an appointment; with galleries, you’ll get a matching row per time block, but we’ll only want the client/treatment mentioned once per block.
> I don’t want nested galleries or lookups to other data sources; nested galleries aren’t great for app performance, so I avoid them unless absolutely necessary. Even then, I’ll try to work around it. Endless lookups from galleries to other data sources can also be a performance drain. So I’ll need a way to build a decent table/collection to show results per day, so that everything can be visualised in a single gallery.
I didn’t find anything amazingly useful via Google for building a time-based calendar, if that’s even the right thing to call it? Of course, now I’ve said that someone is going to drop a blog article in the comments. So the rest of this article details the logic & steps I followed; I’m sure there’s a better way, if you know of one then please reach out.
The brainstorm
If you saw my recent post about the Sequence function, you’ll know there’s a couple of time-based examples in there. This was my base point to start with. The sequence number took a few tweaks whilst I worked out the number of data points between 08:00-20:00, at 15 minute intervals:
ForAll(
Sequence(49),
Time(8, 15 * (Value - 1), 0)
)
Ok, so that works as a starting point for our logic:
However, I’d also like a column in there for the integer value of the relevant time.
I’ve really enjoyed working with integers for matching & filtering data. This goes back to my SQL days; I found significant query performance when matching with integer values instead of text, dates or times. I’ve stuck to that where possible, since.
We have a few options to create integer values relating to our time blocks. In a DateDiff function the unit of time can be represented as days, hours, milliseconds, minutes, months or quarters. I opted for minutes, and we’d expect this value to increase as the 15 minute blocks progressed through the day.
We can calculate this value by taking a time value away from 12:00 AM. I could in theory start this at 08:00 AM to reflect the wife’s starting hours every day, but she might change that in future. So long as the time value is before our daily starting time, we’re golden.
We can test this logic by adding a label to the gallery and adding the following Power Fx:
DateDiff(
TimeValue("12:00 AM"),
ThisItem.Value,
TimeUnit.Minutes
)
Getting there. But these values aren’t in a collection yet, they’re calculated on the fly. The logic is there though so good progress so far:
But, I want a row number as well. We need a way to stop duplicating information for a single appointment, as will be the default gallery behaviour:
I figured if I can compare the current row to the previous row, or the current row to the next row, I can manipulate the border radius properties of the button and make info only appear on the top line:
This is leaning on my SQL experience again; the LAG & LEAD functions were introduced as of SQL Server 2012. I used those a lot in my NHS days for various patient or treatment comparison logic. We could do similar here but would need an incremental row number to make the Power Fx easier.
The build - part 1
Right, so we need the time, the DateDiff output and a row number. I covered row numbers in the same Sequence function article I linked earlier, so I knew I had that method in the bank.
After trying a few different ways, I settled on the following logic:
> Using a With statement to define a temporary table (again, another SQL-ism).
> Use this temporary table to store my time-based entries at 15 minute increments, plus add a column for the relevant integer value from the DateDiff.
> Loop through every record in the temporary table to make a collection, adding a row number at the same time.
Here is the Power Fx in full, building a collection called colCalendarDraft:
// Build colCalendarDraft - structure for times by 15 minutes and row numbers
With(
{
tvBlankSchedule: AddColumns(
// Initial 15 minute Time output
ForAll(
Sequence(49),Time(8, 15 * (Value - 1), 0)),
// Add column with Minute value for current Time less Midnight
"ValueInt",
DateDiff(
TimeValue("12:00 AM"),
Value,
TimeUnit.Minutes
)
)
},
// Build collection based off output from With statement, add Row No logic
ClearCollect(colCalendarDraft,
ForAll(
Sequence(CountRows(tvBlankSchedule)),
Patch(
Index(tvBlankSchedule, Value),
{RowNo: Value}
)
))
);
Here is the output:
I called this ‘draft’ because it’s just the base structure. It doesn’t matter what date is selected, this is the base 3 columns I’ll need, all the time. So all that Power Fx logic to create colCalendarDraft has been added to the OnStart of the app, as we only need to generate it once per session – it’s static after that.
But, we can use this collection to make a ‘final’ collection – and so to the next part of the build.
The build - part 2
In my previous post I walked through building a weekly calendar. When a date is selected from that calendar, we need to take the output of colCalendarDraft to match to scheduled appointments. Appointments information is housed in a separate table.
Here is a sample of the data we’re working with. As per explanations in the previous section, you can see that Date, StartText & EndText all have their respective integer counterparts:
| Client | Date | DateInt | Duration | StartText | StartInt | EndText | EndInt |
|---|---|---|---|---|---|---|---|
|
Ivor Biggun |
7/12/2023 |
20230712 |
30 |
8:00 AM |
480 |
8:30 AM |
510 |
|
Phil McCrevis |
7/12/2023 |
20230712 |
30 |
10:30 AM |
630 |
11:00 AM |
660 |
|
Hugh Jass |
7/14/2023 |
20230714 |
30 |
3:45 PM |
945 |
4:15 PM |
975 |
Our goal is to filter the data by a date column, then plot the relevant entries against colCollectionDraft. We’re going to do this by rebuilding a collection each time, targeting the data we need. However, it’s not as simple as matching StartInt and EndInt, we have to match any potential numbers in-between that range too.
Example – an 8:00 AM – 8:30 AM appointment will have a StartInt of 480 & EndInt of 510. With 15 minute increments, there’s also 8:15 AM in that range – 495. So we need to make sure our matching logic will actually cover 3 blocks in our calendar:
After a few attempts, I settled on an approach to:
> Filter the appointments table by the selected date.
> Create a ‘Final’ collection using ‘Draft’ as the basis.
> Add a column in transit to store the appointment data.
> Additional filtering logic to match any slots in between the appointment StartInt and EndInt values.
With(
{
// Build a temp tbl; appointments for selected date
tvAppointments: Filter(
'Scheduled Appointments',
appDate = gvDateSelect
)
},
ClearCollect(
// Create finalised collection
colCalendarFinal,
AddColumns(
colCalendarDraft,
// Add appointment column, using output of With()
"Appointment",
Filter(
tvAppointments,
ValueInt >= appStartInt && ValueInt <= appEndInt
)
)
)
)
I then remembered our calendar; this Power Fx needs to be called when a date is selected, or when the calendar icon is selected to return to ‘Today’:
We obviously don’t want the same Power Fx being applied to 2 different OnSelect actions, that’s not a good practice. Here’s the way around it:
> Add a button to the screen. In this example, I’ve renamed the button to btnRunMyPowerFx.
> Add the above Power Fx to the OnSelect of the button.
> For the OnSelect of the button in the calendar, and the OnSelect of the calendar icon, add the following Power Fx:
Select(btnRunMyPowerFx)
Finally, set the Visible property of btnRunMyPowerFx to false.
This is a really useful trick to know, meaning your Power Fx is only defined once – therefore only 1 place where changes & maintenance need to be done. Please note: you can only ‘select’ buttons that are on the same screen as the controls you’re selecting it from. The technique does not work if the button is on a different screen.
We need to test the logic just implemented. Firstly, I’ve updated the Items property of the gallery to colCalendarFinal, then added a label to show the client name. For the purposes of the .gif below, I’ve left the button visible so you can see the interaction with it from the weekly calendar and the calendar icon:
Great, it’s working! As predicted though, the name is present for each 15 minute time block relating to an appointment. We need to add some logic to style it a bit better and avoid the repetition.
Row number, you’re up.
The styling
As per the previous posts in this series, some of our styling & config is stored in a global variable. This is created in the OnStart property of the app:
Set(
gvConfig,
{
Background: ColorValue("#FFFCF7"),
Primary: ColorValue("#2B6B31"),
LogoBase: ColorValue("#C4EBD4"),
Negative: ColorValue("#CD5C5C"),
FontText: Font.Lato,
FontHeader: Font.'Lato Black',
FontSizeDefault: 16,
FontSizeHeader: 20
}
);
I’ll start by removing all the labels from the gallery except the one showing the time. Then, I’ll add a button control:
To get our base styling, the button needs some updates to certain properties:
| Property | Value |
|---|---|
|
BorderColor |
Self.Fill |
|
Color |
Color.Black |
|
DisplayMode |
DisplayMode.View |
|
Font |
gvConfig.FontText |
|
Font size |
gvConfig.FontSizeDefault |
|
Font weight |
Semibold |
|
Fill |
gvConfig.LogoBase |
|
Text |
First(ThisItem.Appointment).ClientName |
|
Text alignment |
Align left |
Those changes yield something like this:
Next, we only want the button to show if there’s a matched appointment block. We need to update the Visible property of the button with the following Power Fx:
!IsBlank(First(ThisItem.Appointment))
The next step is to remove the repeating text for each appointment. We also want to manipulate the four radius properties of the button, to give the impression of 1 seamless block. The focus is on the top left hand corner – the RadiusTopLeft property of the button. The following logic is used:
> If the current row doesn’t match the row above, it must be a different appointment so can set RadiusTopLeft to 10.
> If the current row does match the row above, it must be the same appointment so can set RadiusTopLeft to 0.
The Power Fx for the RadiusTopLeft property of the button is therefore:
With(
{
// Get row above current row: row number minus 1
tvPreviousRow:
First(
LookUp(
colCalendarFinal,RowNo = ThisItem.RowNo - 1).Appointment
).ClientName,
// Get current row
tvCurrentRow:
First(ThisItem.Appointment).ClientName
},
// Set RadiusTopLeft based on conditions
If(
IsBlank(tvCurrentRow),0,
tvCurrentRow <> tvPreviousRow,10,0
)
)
Similarly we can set the value of the buttons RadiusBottomLeft property. However, this time we are comparing the current row with the row below. The Power Fx is slightly different for RadiusBottomLeft:
With(
{
// Get current row
tvCurrentRow: First(ThisItem.Appointment).ClientName,
// Get row below current row: row number plus 1
tvNextRow: First(
LookUp(
colCalendarFinal,RowNo = ThisItem.RowNo + 1).Appointment
)ClientName
},
// Set RadiusBottomLeft based on conditions
If(
IsBlank(tvCurrentRow),0,
tvCurrentRow <> tvNextRow,10,0
)
)
To handle the right hand side of the button, we are updating the following properties:
| Property | Value |
|---|---|
|
RadiusTopRight |
Self.RadiusTopLeft |
|
RadiusBottomRight |
Self.RadiusBottomLeft |
Almost there. The relevant corners are rounded or smoothed out, giving the effect of 1 single ‘block’:
Finally, we need to remove the duplication of name. This can be achieved by editing the Text property of the button, with the following Power Fx:
If(
Self.RadiusTopLeft = 10,
First(ThisItem.Appointment).ClientName
)
So that means, if the top left of the button has a radius of 10, show the designated (client name), else don’t show anything. We deliberately leave the ‘false’ part of the If statement above, as we don’t want to show anything if the condition isn’t met.
This is how the wife wants it to look. Happy wife, happy life:
Winner!
It’s worth repeating that yes, our requirements for this article (and indeed, this whole blog series) could be done with an existing 3rd party app. The wife trialed a few but either wasn’t happy with functionality or didn’t want to pay a monthly sub. That’s why we’re travelling down this Power Platform route.
This part of the build was the most challenging. But those unique scenario’s are what help you to learn & stretch the art of the possible. Sometimes just throwing a method out there can spark discussion for making a better one, so hope you’ve enjoyed my stab at it.
In next weeks article, we’ll be tackling adding new appointments and using the JSON / ParseJSON method to save data.
Sean-Paul Bradley
July 18, 2023Great article Craig, will be adding a wee bookmark for future reference!
Anonymous
December 9, 2023This is exactly what I am looking for!!! Thank you for sharing!
Craig White
December 9, 2023Exactly why I blogged about it! I couldn’t find any reference to this when I was looking, so glad I’m able to help others to find it now
Morgan
January 5, 2024Hi Craig, first thanks for this tutorial ! It is exactly what I was looking for.
I’m just blocked with the step to get the start et en time integer. I have both column in this format: “dd/mm/yyyy hh:mm” in a sharepoint list but I don’t succeed to convert this in integer column with the fomula DateDif or Int, maybe you could have an advice for that ?
Thanks in advance
Craig White
January 5, 2024Hi Morgan! Thanks for reaching out, I’ll do my best to help you out. First of all, please can you confirm that the column in the SharePoint list is a ‘Date and time’ format? Or are you storing the value in another column type (such as Single Line of Text)? I’ll then try and replicate my end to hopefully give you a solution!
Morgan
January 9, 2024Hi Craig, thanks for answering !
I succeed to obtain those column now from a date and time column to a calculated one wis this Fx: =((TEXT([Date de début],”h:mm”))-“12:00 AM”)*1440 If this can help someone.
Now I trie to apply this to your code but it’s not working for now. I compared ValueInt to my new calculated value column “Début_integerOK” but there is an error like that, the type of value is not matching. If you have some advice again ? thanks in advance. Have a nice day
Craig White
January 10, 2024Hi Morgan!
I think we need the time value from the DateTime column in SharePoint, then find the DateDiff between that and 12:00 AM to get the relevant integer value. I’ve spun up a SharePoint list with a Date/Time column and got the calculation to work. In my example I’m using a gallery, hence the ThisItem reference but can be mapped to your scenario too. Just make sure you’re using the TimeValue function to convert the SharePoint column to time only:
DateDiff(
TimeValue(“12:00 AM”),
TimeValue(ThisItem.DateTimeColumn),
TimeUnit.Minutes
)
See if that helps?
Sander Daudey
January 10, 2024Thank you Craig for this great blog series on creating a weekly calender view! Great post!
I replaced the gallery with the appointsments by a gallery with flexible height; and adjusted the heights of the buttons according to your logic. So one appointment (or in my case booked hour) is just one button and the subsequent blocks for the same appointment are set to height zero. Works great!
Craig White
January 10, 2024Thanks Sander, that’s a great shout. Your logic is super though and one of the things I want to apply to my wife’s app when I have time. Awesome work! I’ve defaulted to flexible heights for a while but wasn’t something I did with the wife during this build.
Brenda Lopez
August 19, 2024Hello Craig,
Thank you very much for this great tutorial, it’s been really helpful for me, it is just what I was looking for. Eventhough es really great, I am still stucked in one step, right where you create the colCalendarFinal. I added the label with the ThisItem.Appointment and it shows up underlined in red with the “this formula uses scope which is not presently supported for evaluation”. The Power Fx doesn’t bring any error, only when I try to add the label, idk what I am doing wrong 🙁
Craig White
August 19, 2024Hi Brenda, normally that error message occurs when the label hasn’t actually been added to the gallery, but outside of it. Therefore, the ThisItem reference won’t be recognised. Perhaps check where the label is situated and ensure it’s part of the gallery. If that doesn’t resolve the issue, let me know.
Jeremie
April 10, 2025Hello Greg I had the same issue and I add it to the gallery but that doesn’t resolve the issue.
Thank you in advance 🙂
Honza
August 31, 2024Hi Craig, first of all, thank you so much for this tutorial. Helped me so much. But I would like to ask you a question. I’ve got a lot of events going on in my calendar and the events are coexisting at the same time. So I decided to use a nested gallery, because it always showed me only the first event and not all of them. The problem is that I cannot set RadiusTopLeft and RadiusBottomLeft to any event but the first in that day. Do you have a solution for that? Thank you and have a nice day!
Craig White
September 5, 2024Hi, thanks for the question. I’d need to go away and test it out and see what can be done. I only built for a single person so never thought about multiple events at the same time, and the knock-on effect to the design and logic. As a side note, nested galleries can be a hindrance on performance, so it may be you have a gallery per person if using a tablet, or filter a gallery by person. If you absolutely have to see conflicting events, maybe one option is to click on a time block to then load all appointments in that range.
Per Inge Håland
September 5, 2024Found another way to show only text in the first timeslot. Have both the integer value for start and end timeslot in the database table for events (collabEvent).
On the button where the meeting info is presented, used With to get the record from the database and shows the description where the startint is the current timeslot ValueInt.
With({tmpCollabEvent: LookUp(collabEvents, collabEvent = First(ThisItem.Appointment).collabEvent)},
If(tmpCollabEvent.StartInt = ThisItem.ValueInt,
tmpCollabEvent.Subject &” “& tmpCollabEvent.Responsible.’Display Name’,
“”)
)
Craig White
September 5, 2024Nice work, thanks for sharing!
Mikaela Ward
November 13, 2024Hi Craig,
I get stuck at adding the row number in – is the time based calender a gallery or a table?
Sorry – newby here!
Mikaela
Craig White
November 14, 2024Hi Mikaela, it’ll be a gallery to use to leverage the data from the collections? If that’s what you’ve tried & still stuck, let me know!
Mikaela Ward
November 27, 2024Thanks Craig, I am now in a pickle with getting the ‘Client Name’ to display, or in my case the ‘Customer Name’.
I am able to get it by using the following formula : LookUp(Bookings,Booking = First(ThisItem.Appointment).Booking,Customer)).
I cannot use First(ThisItem.Appointment).Customer, as it says it utilises scope and does not display anything.
I have looked at the collection table and previewed the appointment data it has pulled through, and it does not seem to have collected details such as this under ColCalenderFinal, it just pulls through the Start and End Integer times, the date and the primary column being the “Booking ID”.
How can i go about collecting other fields such as my customer name and other columns ?
Hopefully this makes sense…a bit hard to word!
victor
January 3, 2025Been there. Ban the Explicit column selection in the stopped features.
Dev
January 30, 2025Amazing work Craig , don’t suppose you’ve made a video tutorial at any point? I’m quite new to learning Power Apps and would love to see a video tutorial of this fine piece of work.
Craig White
February 9, 2025Hi Dev, I don’t have a video for this one. I opted to blog as I’m able to provide the code snippets for others to copy & manipulate. With videos, you’d have to type it all out yourself.
Brian P
February 5, 2025Hi Craig! Great tutorial and write up! I was wondering if you ever thought to add in logic somewhere so that a new appointment doesn’t overlap an existing appointment? I recreated this app and it seems that if a treatment is booked with a duration that bleeds into the next appointment, it still allows for it.
Wondering how you would approach that. I’m currently working through it now so will post some feedback if I get to a solution.
Craig White
February 9, 2025Hi Brian,
On the screen where we’re building a schedule out, I have a hidden label with the below code:
With({tvScheduled:Filter('Scheduled Appointments', appDate = gvDateSelected && appStatus = 'Appointment Status Choice'.Arranged && appID gvSelectedAppointment.appID),
tvProposedEndInt: Value(DataCardValue16.Text)},
// Counts if proposed end time clashes with another appointment
CountRows(Filter(tvScheduled, gvStartTimeInt > appStartInt && appEndInt > gvStartTimeInt)) +
// Counts if proposed start time clashes with another appointment
CountRows(Filter(tvScheduled, tvProposedEndInt > appStartInt && appEndInt > tvProposedEndInt)) +
// Counts if proposed state time and end time clashes (helps for 15 min appointments)
CountRows(Filter(tvScheduled, gvStartTimeInt = appStartInt && DateDiff(TimeValue("12:00 AM"), TimeValue(gvStartTimeText) + Time(0, Sum(colTreatmentPackage, Duration),0), TimeUnit.Minutes) = tvProposedEndInt)))
Effectively this tries to capture any clashes with current appointments that might start or end within the proposed slot. If the count > 0, the button is disabled & I show an error message
Mostafa Fadlallah
March 5, 2025Hi Craig, very helpful list of article, helped me design a scheduling solution that I needed. So thank you for that. I have a question that got me stuck, what if I want the values of the schedule to be dependent on the user’s timezone? the Int values would be wrong, maybe the solution for this is to generate the StartInt and EndInt data before loading the calendar?
Craig White
March 6, 2025Hi Mostafa, good question! I built the solution solely with my wife (GMT) in mind but yes, at first glance/thought, generating the Start & End Int before loading the calendar could work. If you’re giving that a try, let me know how you get on!