Director Accounting Insights Ltd.
Share this content

Can you learn Power BI in a day?

Getting started with Power BI requires little to no prior knowledge. However, peel back one layer and you soon realise that the product has many more layers underneath. Hugh Johnson outlines the most useful things you can learn in a day on Microsoft’s powerful visualisation tool.

2nd Oct 2019
Director Accounting Insights Ltd.
Share this content

Like most questions to do with Power BI, the answer is “it depends”. It depends on what you mean by “learn”. If all that you want to do is to read or use a report that someone else has produced and maybe change some visualisations and styles, then yes you can.

However, if you want to use Power BI to create reports, the answer is quite different. I have been using Power BI pretty much full time to build financial reports for nearly five years now and there is barely a day that goes past when I don’t learn something new. 

So, assuming that you do want to create your own reports, let’s change the question to “What are the most useful things about Power BI that you could learn in one day?” To answer this, let’s assume that you are an accountant, a very accomplished user of Excel, but have little to no experience of Power BI.

The most useful things about Power BI that you could learn in a day

I have given this particular question a lot of thought and the answer, in my opinion, boils down to a few key things:

  • Conceptually, how are Excel and Power BI different?
  • The Power BI Filter Context
  • How to write formulae in Power BI
  • Filtering and manipulating dates

In one day, you cannot become an expert on any of these topics, and there are many very important things that you would have to ignore. But I believe that in a day you can gain a practical appreciation of these four points that will enable you to get started with your report-building, facing in the right direction.

Power BI is like an onion

Like many great pieces of software, such as Excel, getting started with Power BI requires little to no prior knowledge but, just as you peel back one layer, you soon realise that the product has many more layers underneath. And yes, like an onion, it can make your eyes water, both from the joy of producing a really great report and also out of the frustration of not getting the results you need.

Being such a deep product there is a lot to learn so, in my opinion, the best way to start is with a few key basics and an appreciation of the greater depth that is there for when you need to progress.

The first thing that I recommend you try with Power BI

The first thing that I ever did with Power BI, was to load a spreadsheet of sales invoice transactions that I had exported from Sage 50 and have a play. If you haven’t already done this (or similar), I would recommend it.  It is very gratifying! In a few minutes, and without writing a single formula, you can end up with a report like this:

power bi image 3

When I am teaching my “Introduction to Power BI for Accountants” course, I get my students to do this before anything else. Not only is there an immediate sense of accomplishment, but it also surfaces some very fundamental principles about how Power BI performs calculations on your data. It introduces the concept of the Power BI Filter Context. In the chart above, without writing a single expression, I have got Power BI to perform a calculation SUM ( TransactionLines[NetAmount] ) 110 times, get 91 different values and display the results.

Formulae in Power BI

In the example above, I have introduced perhaps the most fundamental difference between Power BI and Excel. In Excel, you can predetermine the result of a formula by reading it and tracing back through the cells that it references. You cannot do this in Power BI, where the result of any formula will depend on the context in which it is used. 

Our example sales report uses the same formula (that I didn’t even write) 110 times and gets 91 different values. If you want to calculate profit and loss, for example, this means that you can create a generic P&L calculation that Power BI applies to whatever nominal codes and time periods are selected. The image below shows a generic P&L formula that I wrote for a dataset from Microsoft Business Central.

power bi image 2

If you are not already working with Power BI, I don’t necessarily expect you to understand this formula other than to notice that there is no mention of any date or nominal code. It is a generic formula that you can present by date, period, year, nominal code and category in a visualisation on your report (provided that you have a few basics set up first). This is, in my opinion, the single most important thing to “get” in Power BI, and it should definitely form part of what you learn right at the start.

Fortunately for Excel users, Power BI formulae look somewhat familiar. You will write most, if not all, of your formulae in Power BI using DAX (this stands for Dynamic Analysis Expressions), which is very Excel-like, but there are differences.

In Excel, you use a formula to compute a specific value in a specific cell.  In Power BI, you can do a lot more with a single formula. You can use a DAX formula to compute single values (these are called Measures), entire columns (these are called Calculated Columns), or even tables with many columns and rows (these are called Calculated Tables). This gives you a lot of power to do a lot more with a relatively small number of formulae, but also more things to think about. You should include in your very early education about Power BI an appreciation of the difference between a Measure and a Calculated Column, and when to compute which.

Filtering and manipulating dates in Power BI

As an accountant, you will work with dates a lot in Power BI. You will most likely want to summarise data by month, quarter, year, YTD, previous periods etc. Microsoft has included in DAX a range of “Time Intelligence” functions to help you to do this.  Examples include:

  • CLOSINGBALANCEMONTH
  • DATESTYD
  • DATEADD
  • PARALLELPERIOD
  • ENDOFMONTH

All in all, there are 35 Time Intelligence functions and you can see the full list here. They look useful, no? Well, they are, but there are some basic things, like setting up your Dates table, that you need to put in place in your model first.  If you don’t do this, the Time Intelligence functions simply won’t work. What is worse is that they might look like they are working, even though they are giving the wrong answers. 

Now, just as when you learn any new language you find that you can express the same thing with different words and in different ways. This is also true with DAX and particularly its Time Intelligence functions.  This is a personal thing, but the only Time Intelligence function that I tend to use is DATEADD. I find that if I have my Dates table set up correctly (something that you should include in your one-day learning), then I can use the combination of CALCULATE, ALL, FILTER and DATEADD to generate any date filter that I want. This doesn’t make it better or worse than using other Time Intelligence functions that are essentially, as I understand it, the same thing under the hood. 

The really important thing that you must learn very early with Power BI is how to set up your Dates table, how the Filter Context works, and how to use the function CALCULATE with Time Intelligence functions to get the date filters that you want.

Relationships

A dataset in Power BI is a collection of tables of your data and relationships between these tables. This in itself can be quite a big subject, but you will need at least a basic understanding of this the moment that you add your (essential) Dates table to your model. Any date-driven visuals in your report will almost certainly have a filter context that depends on relationship(s) with your Dates table. 

Row Context and Evaluation Context

What I omitted to say earlier in this article, is that there are actually two contexts that Power BI takes into account when calculating values.  These are the Filter Context that I have already mentioned and what is called the Row Context.   Together, these form the Evaluation Context that defines what data is available to a formula when it runs and therefore what result is calculated.  One day is not enough, in my opinion, to delve into the Row Context.  This is ok though.  If you limit yourself initially to creating Measures to produce your aggregations (which is generally what you should do anyway) and simple Calculated Columns to set up your Dates table, then you don’t need to worry about the Row Context on day one.  It is there, but you don’t need to worry about it.  You will need to learn about the Row Context if you want to do some kind of aggregation or ranking within a Calculated Column (for example, if you want to classify your customers based on their profitability), or create a Calculated Column that references other tables (for example a customer profitability calculation in the customers table may need to reference data in a Sales table and a Products table).

Summary

Learning Power BI is, in my opinion, like learning to Ski.  In one day, with focus, attention and maybe a few coffees, you can pick up the essential techniques that you need to get started.  You will probably still feel a bit wobbly, but after a few days practice, you will amaze yourself with what you can do.  How accomplished you want to become after that is up to you. J.  If you learn to ski, then with practice you will develop muscle memory that makes it feel more natural each time you do it.  Power BI is the same, but the “muscle” in this case is your mind.  Learn it, use it and your tears will be those of joy and not frustration.

Useful links

DAX function reference

Power BI - How and why to create a date table

YouTube video - sorting months according to your financial year

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.