Often, I like nothing more than a challenge, so if someone asks “is this possible?” or “can we do this?” my mind starts working ahead trying to figure things out. Sometimes I regret this later on, but I cannot help myself beginning to put thoughts together to figure out if it will work out! Recently I was asked to look for a way to see annual leave across a team with dates and current balances, possibly even with a calendar.
To give you a bit of background, I am currently working on a system implementation project with a team that is made up of colleagues from different departments from across the University. At the moment, recording of annual leave is not centralised and is held locally withing each department, so we don’t actually have an overall picture of annual leave across the whole project team.
If felt the easiest way to do this would be to create a spreadsheet with a start date and end date as columns for each holiday and a list of names down the side. If this is the first CI blog that you have read, then you will not know Why I don’t like Spreadsheets. I’m not averse to spreadsheets, they have their place in accountancy and finance, but I don’t believe they are ideal for keeping track of annual leave, so, I wasn’t even going to consider creating a spreadsheet. I wanted this to be as automated and as innovative as possible.
After thinking about it, I decided that a SharePoint List, along with a Power BI dashboard was likely to provide what was required. What I like about Lists is that they work well with Power BI, they are easy to use and to update, and they have many useful in-built features. For example, you can create a calendar view that shows dates, or you can create notifications when something is updated, or use the form within the List that can be used by anyone to add items to the list using a link.
The other thing to consider is that I was asked to help a newer colleague learn to use things like SharePoint Lists, Power Automate and Power BI. Working together on creating this holiday plan was a great way for her to learn. Certainly, for me, when I was learning how to use these tools, I found two things:
- I needed to have something that I knew it could be used for, and
- I learned it by doing it myself.
We started discussing how best to capture the information on the List and how we could display it on a dashboard. We were able to do this very easily and import it into Power BI and show the dates that project team members were on annual leave.
The other thing that we had been requested to do was to display remaining holiday balances, so we needed to add in being able to calculate this without taking off weekends or dates when the university was closed. This was certainly way more complicated than either of us anticipated and we had to call in help from another colleague. When doing something like this, it is always best to collaborate with others, use their knowledge, rather than sit and struggle trying to figure it out by .
If you want have a look at some of the other things we have done in the past by looking at our case studies and previous blogs:
