Chris Hatzis:
Excel Tips, a CPA Australia podcast.
Neale Blackwood:
Welcome to the Excel Tips podcast. My name is Neale Blackwood and in this episode we're going to look at using DAX formulas to create accounting reports in Excel. Now I do recommend you check out the INTHEBLACK article. There's a link to a previous article on using DAX. I'll explain what DAX is if you're not sure a little later, and Power Pivot. Now that example used sales as an example. Today, I want to talk about using DAX and Power Pivot for accounting purposes because that can be a little bit more complex than sales.
The companion video goes into a lot of detail and explanations. It's actually one of the longest videos I've done for an article. It goes to about 29 minutes, but it does cover a lot of different techniques that you can use with Power Pivot and DAX. DAX is a function language that is inside Power Pivot, it's also inside Power BI, and it allows you to create what are called measures. We need to talk about these Power things. Microsoft unfortunately decided to label everything with Power at one stage, and so we have Power Query.
Now that is the best way to import data into Excel. It's also exactly the same way that you can import data into Power BI. Power BI, the BI stands for Business Intelligence. Power BI is a separate package to Excel and it has better dashboarding techniques. But it also has, as well as Power Query, it also has the same engine as Power Pivot. So Power Pivot is in Excel, but the same engine that's behind Power Pivot is also in Power BI. So both Power Query and Power Pivot are part of Power BI, but that's separate to Excel. Within Excel, we have Power Pivot and Power Query. And within Power Pivot, we have the DAX formula language.
Everything you learn for DAX in Excel is applicable to Power BI as well. DAX, by the way, stands for Data Analysis Expressions. So these are special functions that work with tables basically. So if you want to think of Excel as two-dimensional, so we've got rows and columns, you can think of Power Pivot as being three-dimensional because you've got rows, columns, and then multiple tables. Now the beauty of using Power Pivot, which is how you access Excel's data model, which sits in the background of Excel, Power Pivot allows you to relate tables together. So it's like a relational database. It's not a relational database, but it works like it. And you can report on data like a relational database.
Now the article and video goes into a lot of detail about how you can use Power Pivot to build accounting reports. Now, accounting reports are a little bit more complex than sales reports and there's two issues that you might face when you're building accounting reports. And one of them is financial year. And so, if you create a date table, you can get around the financial year limitations of pivot tables. Pivot tables are built to work with calendar years, but by using a date table, you can get around the limitations of calendar years and you can report by financial year.
One of the problems you might face when building accounting reports with Excel is that trial balances often have credits as negative and debits as positive. But when you need to build your report, you still need to show, for example, revenue as a positive value, whereas the trial balance might have it as a negative. And so you can use a account table which allows you to map the accounts and change the values so that you can display them correctly in the accounting reports.
Now the solution used three different tables. There was a trial balance table, which had multiple trial balances in it, an accounts table, which was basically a chart of accounts. That had a column in it that allowed you to have a sign for the account, and so each account has a sign that you can use to adjust it. So all the revenue accounts had a minus one as the sign. And so that allowed the report to show the revenue as a positive value. And there was also a date table. And so those three tables were related together in Power Pivot. After you've related the tables together, you need to build the DAX measures.
Basically, the best practice is that every value you put into a Power Pivot report should be a measure. So you need to create a formula to calculate it. Now the beauty of it is you only create one formula per measure. So in Excel, we're sort of used to creating a formula and then copying it down and across. But in the DAX language, you basically create one single formula and it does all of the calculations for that particular measure, and you just drag the measure onto the report and everything works.
Now, there were quite a few different measures created, revenue, direct costs, indirect costs. There was also some margins and some profits and also percentages. Now, one of the things you need to do when you are creating measures is to test them. So you can actually just use a normal pivot table to do the testing and you can compare the raw data with the measure data. And, again, there’s an example of that in the companion video. One thing that can help you is AI because AI can write DAX formulas and can even review DAX formulas and explain them to you as well. So if you've come across a DAX formula in your file, you can get your AI to explain the formula to you.
So once you've set your tables up and written your DAX measures, you need to test them. And as I mentioned, you can do that with Excel's normal pivot tables and also just some general formulas. That's one of the advantages. When you're writing measures in Excel, you can actually use formulas and functions to sort of test them on the spreadsheet, which is a little easier than creating measures in DAX in Power BI because it's harder to check back to the original data source.
Now, when you're creating measures, there is one rule that you need to follow. Inside a DAX formula, measures are referred to using square brackets around them, and fields or columns... So fields and columns are the same thing in DAX, and a field or a column should be represented by the table name followed by the field or column name within square brackets. So that means when you're looking at a formula, a DAX formula, and if there's square brackets around a name, that means it's a measure. And if there's a table name followed by the brackets with a name inside that, then that's a column within the data. So that's a best practice for creating your DAX formulas.
So when you are working in Power Pivot, there's sort of a setup phase where you're importing the tables and relating together. Then there's a build phase when you're building your measures. Then you need to test them and then you need to basically run and maintain them. So that's sort of the phases that you'll go through when you're working with DAX measures. In terms of the maintenance, you will need to add any new accounts to the accounts table to make sure that they are treated correctly in the reports. You can also add slices to your pivot tables to allow you to filter the reports as well.
So if you're interested in learning about Power Pivot and DAX, then I recommend you check out the article because you can check out that initial article, which takes you through setting up Power Pivot and some of the basics. And then this most recent article will take you through a more advanced application where we're looking at building a simple profit and loss report using DAX formulas and Power Pivot. Thanks for listening.
Jackie Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.
