Click Here to be taken to the LDI Quizzes and Courses for this syllabus
Power BI Financial Reporting (Advanced)
This course will take an experienced Power BI developer (minimum equivalent experience of the content in the DA-100 Syllabus) and show how to apply a series of intermediate/advanced techniques in Power BI in order to build financial reports and analysis.
The content is designed to be broad – considering everything from developing the required data culture for self-service BI to using custom visuals for write-back capability. It will also go deep into particular topics such as data modelling and storytelling with data.
The content is organised as a series of “patterns” – each designed as a micro-learning module to explain the scenario, the steps you need to apply for that specific technique (explained with video/text and interactive elements) and the opportunity for you to try them out where this is feasible.
The content will continually evolve as new features are released in Power BI. The content is produced by Rishi Sapra (MVP) and the PPF collaborators listed on www.powerplatformfinance.com
See the video below for a high level overview of some of the concepts covered in the first few topics of this syllabus
Produce User Stories and Complete a scoping template (Finance Use Case)
Description
The micro-learning course modules will first go through identifying the set of requirements for Financial Statement reporting in Power BI through capturing the audience/user stories and the key questions that you need the report to answer.
It will also then look at conceptually designing a data model using a Bus Matrix/Starnet.
Problem Statement/Use Case being addressed
Too often, people start a Power BI Project with putting the data straight into a report and producing visuals; starting instead from understanding what your end users want will ultimately result in significantly better designed and utilised solutions.
Before you can shape and transform data using the Query editor, you also need to know which end tables you’ll have and what the relationships will be between them – also known as the data model in Power BI. This module will allow you to conceptually design a data model that will be the blueprint for your data shaping/transformation work
Developing a Self-service Architecture for Finance
Description
This topic focuses on non-functional requirements for the reporting, including the architecture required for dealing with large data volumes (e.g. considering Direct Query/Aggregations as an alternative to Import mode), and the options for distributing the reports and data to colleagues. This topic will present a view on the workspace structure in Power BI required to enable analytics self-service analytics making use of Dataflows and Shared Datasets. It will also look at the operating model and Roles & Responsibilities required in the organisation in order to drive the right data culture, and security options to ensure that there are appropriate controls and that only people who should have access to the data are able to see it. Whilst this is covered in detail in the LDI Power BI Governance course, this topic also considers this at a high level given its importance to a finance function.
Problem Statement/Use Case being addressed
The ease of being able to connect to data and build reports is the real value proposition of Power BI though, much like with Excel, this approach of everyone building their own data/reporting solutions for every use case is not without its challenges. In many scenarios, it makes sense to centralise the data and logic with data models managed by Power BI experts and/or a centre of Excellence team. It is also important to consider the Architecture you need with reporting solutions to meet with the non-functional requirements around data refresh and ensure performance particularly when working with large data volumes and complex logic as is often the case with finance solutions.
Connecting to Financial Data
Description
This topic is focused on building dataflows, showing examples of connecting to data/performing transformations from a range of sources – an on-premises SQL database for core financial transactional data, Excel files stored on Teams/SharePoint and Azure Blob Storage, as well as exchange rate data coming from XE.com/Alpha Vantage. We will use Column from Examples/a custom column to automatically extract the date from the file name and look at good practice for connecting to various data sources
Problem Statement/Use Case being addressed
Dataflows provide a layer of abstraction between data sources and Power BI, allowing data connections to be set up centrally and access to the data to be managed via workspace access rather than providing credentials to analysts to connect directly to the source systems such as transactional databases. The challenge is then how to combine that data with other sources – perhaps local/business function specific data which is mastered in Excel files that your team manages, or external data from websites/APIs. The format of all this data might not be readily suitable for reporting and may need to be re-shaped and cleaned before it can be fed into central data models upon which reports and analytics in Power BI can be built. Dataflows provide a way to perform data shaping and logic centrally in a manner that is transparent, maintainable, and re-usable across multiple teams and use cases.
Creating a Star Schema from the Data
Description
In this module you will take the finance transactions we built in the Dataflows module and build a General Ledger and Trial Balance from it.
This exercise is focused on the Power BI’s query editor which is needed to address the cleaning and transformation of our data into the necessary General Ledger and Trial Balance fact and dimension tables required to design our financial reports/dashboards. We start with an overview of common transformation methods in Power BI’s query editor and then we will dive in on how we utilize these transformation methods to create our dimension and fact tables for our General Ledger/Trial Balance data models.
Problem Statement/Use Case being addressed
The structure of the data that comes from financial systems is often not in a format optimised for BI reporting and needs to be reshaped. This is particularly the case where you are brining data in from different sources (e.g. actual and budget data often comes from a separate source and is often at a different level of granularity) – part of the exercise you will need to carry out in the query editor is to combine numeric data, either brining it to the same grain or keeping them at a separate granularity linked with common dimensions
Building and Tracking Core Metrics
Description
We will start with storyboarding and looking how to design our page/decide on the visuals that we want to show, though the focus of this module is calculating financial metrics (Margins, KPIs etc) using DAX in Power BI. This will start with building base measures and then using CALCULATE to change filter context. It will also look at how to create time-intelligence based measures (MTD/QTD/YTD) using switch/disconnected table or Calculation Groups.
Problem Statement/Use Case being addressed
Whilst simple figures which relate to GL Accounts (E.g. Revenue) are simple to calculate in Excel on a row-by-row basis, performing ratios and more complex calculations (e.g. Gross Profit Margin %, AR/AP Turnover, Return on Assets) – and viewing these by different slices (e.g. time period, customer) – is much harder. This is where the real power comes from doing calculations in DAX measures instead.
Building an Income Statement using the standard Power BI Matrix visual
Description
In this module you will use the data model created above to build out an Income Statement as a matrix visual in Power BI. This will include using the reporting lines table, and SWITCH/Calculation groups for sub-totals/custom calculations. This exercise will cover the DAX and matrix formatting options required to show formatted Financial Statements along with a drill-through into Journals to see the underlying journal entries/perform an analysis of these against a given account. Taking this to a more advanced level includes hiding levels of the hierarchy for which there is no data (ragged hierarchies)
Problem Statement/Use Case being addressed
By default just building a matrix visual with accounts on rows and values will not give the required format of financial statements – for example sub-totals/custom calculations will be missing and just presenting your Chart of Accounts hierarchy in the visual will not give the layout required. There is a temptation to just present these in Excel so that you can get the proper layout/formatting, though it is also possible to achieve this in Power BI!
Building an Income Statement with Custom visuals, Paginated Reports and Excel
Description
Whilst it is possible to present a Financial Statement using the standard Matrix visual, it requires a lot of customisation and clever DAX, which is not always an optimal solution. A number of custom visual developers/vendors have produced custom visuals (both paid and free versions) which are better suited to showing Financial Statements (E.g. they allow you to add custom rows). In this module we will review solutions from Profitbase, Inforiver and Zebra BI. We will also look at how to present the Income Statement as a Paginated Report (a pixel-perfect report which can be printed/archived) using the Power BI Dataset as an example.
Finally, we will look at utilising the “Analyze in Excel” capability to connect Excel to the Power BI Dataset and build our income statement there.
