Power BI

Aim of course – to teach fundamental theory and knowledge of Power BI and then apply this along with techniques to create a variety of reports relevant to accountants and their monthly reporting duties a.k.a. ‘month-end’.

Month-end context – each section of the course begins by framing the learning in the context of the problems and challenges faced in traditional financial reporting approaches (e.g. Excel) and the solutions Power BI provides.

The Company – the course is based around a fictional gym equipment wholesaler, Workout Warehouse, and its customers. The course data sets have been tailored to allow the creation of reports that explore revenue and operating cost performance and tell a story – as you would in your month-end reporting.

Course data – is comprehensive and realistic and comprises of actuals, budget, forecasts and prior year data. It includes account codes and cost centres to add realism and relevance to the audience, as well as customer and staff characteristics to allow for more commercial analysis and insights. There is also a ‘big data’ set to showcase Power BI’s ability to leverage large quantities of data.

Course resources – aside from data, there is a downloadable 92-page slide deck that the course is built around, several Power BI files capturing various stages of course progression and other support materials.

Power BI Desktop vs Service – 90% of the course is concerned with Power BI Desktop, the application used to create  reports. 10% at the end is dedicated to introducing Power BI Service, the cloud-based platform used to store and distribute reports.

Report build, quizzes & challenges – you are encouraged to put into practice what you’ve learnt by following along with the report build as you go through the course, test your knowledge with regular quizzes and familiarise yourself with using the reports you create by answering commercial questions in the challenges at the end of each.

Intro to Power Query – overview of the application used to connect to data sources.

Importing data & making transformations – the steps taken to connect to data, makes edits or transformations to it in Power Query Editor before finally importing it into Power BI.

Creating a timeline table with a blank query – a brief introduction to M code, the programming language used by Power Query, and how it can be applied in a blank query to create a table of data to later be used in Power BI.

Adding a new month of data – demonstrations of three different ways to add a new month of financial data to your reporting i.e. to update your reports at month-end.

Data model theory – a look at what a data model actually is, its benefits, and the characteristics of the tables involved – lookup vs fact tables, primary vs foreign keys etc.

Creating table relationships – demonstration of how to construct a data model in Power BI.

Filter flow – theory and explanation of how filtering works in a data model and its relevance to the tables and the type of data contained within them.

DAX operators, calculated columns & measures – an intro to DAX, the formula language of Power BI. We look at the role played by DAX in creating our reports, examine the syntax and start experimenting with a few functions.

Measure tables – a great and super important technique we use to keep the measures we create organised and easily retrievable.

Filter context – a through explanation of what filter context is and how it works to create the visualisation you see and interact with.

Most useful functions – with over 250 DAX functions to choose from, this is a helpful quick overview of the key ones. Some will be used in the course whilst others are for your own experimentation as you grow in confidence.

DAX techniques – throughout the DAX learning we’ll be looking at tips and techniques such as formatting our code, optimisation and a technique known as ‘measuring branching’.

Reports 1-6 – we’ll see how we must add to the data model, create more DAX and call on a range of visualisations and techniques to produce reports tailored to a month-end financial reporting pack that really adds value. The reports we’ll create are: Departmental Analysis, Revenue Summary, Customer Analysis, Profitability Analysis, Departmental Opex and an Executive Summary.

Visualisations – a number of visualisations are explored and utilised across the reports to give ideas of which work best when presenting different types of data and analysis.

Actuals vs Budget/Forecast – given our financial reporting rarely consists of only actuals, but rather compares them to a budget, forecast or prior year, we look at how we can bring the latter into our reports, quickly toggle between them, and create variances to the actuals.

Period, QTD, YTD – a brilliant technique that will allows us to select whether our report shows financials for a single period, Quarter-to-Date or Year-to-Date, allowing us to quickly toggle between these as we present.

Dynamic commentaries – an excellent (and original!) hack that allows us to integrate dynamic commentaries into our reports that change as we change the time period we’re presenting or the comparison we show our actuals against e.g. Budget, Prior Year etc. This ensures that the appropriate commentaries are always adjacent to the figures we are showing.  

Variables – a look at how variables can be utilised in our DAX code to improve its performance, efficiency and/or readability.

Row-level security – this will allow us to restrict the data and information that defined users can view in our reports. For instance, anyone in the marketing team can only see marketing costs in our Departmental Opex report.

Intro to Power BI Service – the last part of the course will cover how to we can publish our reports to the cloud, distribute them round the business and the many advantages of doing so.