GETTING PUBLIC HOLIDAYS & LEAVE INTO POWER BI
Public holidays and leave can be stumbling blocks when reporting. Reports dealing with budget performance, calculating work days, ensuring key dates are met and managing capacity can all be affected. While some practice management systems will allow you to set public holidays, others don’t have this functionality. Leave has the added complexity of unknown dates to be taken through out the year. In addition, it is often held in systems separate to your practice management system.
Is there a way to bring that data into one place and use it to increase report accuracy? Taking into account both leave days and public holidays? Power BI, with its ability to access and join disparate data sources, offers a solution.
Let’s look at Public Holidays first
There are data sources available online which will provide you with a list of public holidays in a machine readable format. This means that you can access that information directly and feed it into your reports. It also means that you can rely on these datasets being updated, rather than having to update that data yourself.
The two that I believe are most relevant in Australia, are:
The Microsoft Azure public holiday dataset – https://azure.microsoft.com/en-us/services/open-datasets/catalog/public-holidays/#AzureDatabricks
The Australian government’s https://data.gov.au/dataset/ds-dga-b1bc6077-dadd-4f61-9f8c-002ab2cdff10/details
I use the second dataset via an API call and bring the resultant holiday table into Power BI. From here, the holiday table can be joined with other date information to give an accurate indication of working days.
If your firm has offices over in different states then you can join this public holiday information based on the location of the fee earner. This will ensure reports reflect working days across all offices.
Be aware that these datasets do not include council holidays. For example, show days or other local council holidays. So you will need to think about how you account for those if they exist.
What does access to public holidays allow you to do?
Having up-to-date public holiday information automatically feed into your reports means that your working day calculations are accurate. This in turn can inform budget planning and budget tracking. It can give you and your fee earners an accurate and fair indication of how they are travelling against budget. It can also help with the setting of monthly budgets.
If you are using Power BI to track dates and assist with matter management, any key day working day calculations will be accurate for the correct jurisdiction.
It can also assist with understanding cash flow and how it is impacted by public holidays.
Let’s take a look at leave
Leave is often kept in systems separate to your practice management system. Some firms choose to record leave within their time cards. But it’s often difficult to access that specific information in a meaningful way.
As long as you can access the data in your HR or accounting system, you can provide a leave table to Power BI. You might be able to do that through a direct connection, or by exporting leave to an excel report and connecting to that. If your firm records leave within time cards, then Power BI can query those time cards that are flagged as leave.
What does have access to leave allow you to do?
Similarly to public holiday information, including leave in budget tracking allows you to give a fair and accurate update to your fee earners.
When capacity and project planning, it can be very useful to understand who is on leave. It can also assist in ensuring that key dates are not missed when a particular fee earner might be on holidays.
