How to create accounting reports with DAX

From trial balances to profit and loss statements, reporting sits at the centre of modern finance. This episode explores how finance professionals can use DAX (Data Analysis Expressions) formulas and Power Pivot to build more powerful accounting reports in Excel. Learn how to create scalable, maintainable reporting models using Excel’s data model, while applying techniques that can also be used in Power BI. Some of the key learnings: • Understand the relationship between Power Pivot and DAX • Build accounting reports using multiple related tables • Overcome financial year limitations in pivot tables • Correct debit and credit sign issues in trial balances • Test and validate DAX measures using pivot tables • Use AI to write, explain and review DAX formulas Listen now to improve your Excel skills. Host: Neale Blackwood CPA. He has more than 20 years of experience as a Microsoft Excel educator and is the author of more than 200 INTHEBLACK articles as well as a book, Advanced Excel Reporting for Management Accountants. [LINKS] For more, check out the accompanying YouTube video of this episode. **Phil T to add Read more of Neale’s Excel articles on INTHEBLACK, including this story that explains DAX in further detail. Loving this content? Don’t forget to subscribe to EXCEL TIPS and share this episode with your colleagues and friends in the business community. Would you like to listen to more Excel Tips and other CPA Australia podcasts like INTHEBLACK and With Interest? Head to CPA Australia’s YouTube channel. And don’t forget to subscribe to the channel. You can find a CPA at our custom portal on the CPA Australia website. CPA Australia publishes four podcasts, providing commentary and thought leadership across business, finance and accounting: With Interest INTHEBLACK Excel Tips INTHEBLACK Out Loud Search for them in your podcast platform. You can email the podcast team at [email protected] Chapters: 00:00 Intro 00:31 Handling value signs correctly when credits shown as negative. 00:53 How to handle financial years correctly in PivotTable reports. 01:21 Using the DAX function language in Power Pivot. 01:59 Excel works in two dimensions - rows and columns. Power Pivot work in rows, columns and tables. 02:26 Power Pivot is like a relational database. 02:33 Three tables used, Trial Balance, Accounts and a Date table. 03:18 The Accounts table has a sign column that will be used to adjust negative values. 03:55 First step is to load the three tables into data model. 05:15 In the Power Pivot window use the Diagram View to relate the tables together. 06:41 Create a PivotTable to check total the total of the Trial Balance. 07:47 Best Practice to create a measure for each value you want on the report. 08:26 Create a Report Amount measure to correct the sign. 09:19 SUMX function is an iterator function. It can perform a calculation row by row in a table. 11:27 A DAX measure can be formatted, ready for use in a report. 11:51 Check the formula to see if the syntax is correct. 12:22 Measures are added automatically to a PivotTable if it is active. Useful to check Measures. 14:05 The Report Amount measure will be used by the report measures. 15:05 The Revenue measure uses the CALCULATE function like a SUMIFS function. 16:35 Measure names shown between square brackets. 16:45 Fields or columns use the table name followed by the field name in square brackets. 18:38 DIVIDE function avoids divide by zero errors. 20:21 The percentage measures work in total but not by account. 22:01 Creating the simple Profit & Loss report. 22:28 Measures listed with an fx icon in front of them. 23:09 Switch a horizontal PivotTable report to a vertical report. 24:35 Technique to copy a report. 25:15 Add a State filter to the report or use a Slicer to filter the report. 26:48 AI can assist in building and explaining DAX functions and formulas. 27:06 PivotTable Fields List dialog layout options. 27:29 DAX requires a setup phase, a testing phase and a maintenance phase.