Notice that we also added a column for ‘Last Refresh Date.’ This will be useful for the company to know, so it will be added to the Power Bi Report later. In the function, also notice that the date has not only been converted to a standard format but has also been adjusted for time zones (the corporate office is in a different time zone than the stores). By adding the CREATE VIEW statement above the SELECT clause, we can create our new view and move on to Step 2.

Step 2: Create a Data Model

We will be using the Power Query editor within Power Bi for our data modeling. Once the staging query has been uploaded to Power BI, we can use this as the source of reference for our fact and dimension tables. The five initial tables we defined can be seen in the screenshot below. The only table which did not have a relationship automatically defined from Step 1 is ‘DimDate.’ We can add DimDate to the model through a one-to-many relation between DimDate’s Date column and FactGLTran’s GLTranDate column. We also hide certain fields on the many sides/star side of our relations. This reduces redundancy by ensuring that fields on dimension tables are not also represented in the fact table. You can tell which fields are hidden in our report by the ‘crossed out eye’ symbol on our date model. Measures created using from our fields by using Data Analysis Expressions (DAX) are added to the FactGL Table and are represented by a calculator icon to the left of the field. The schema that we have created below is a star schema:

In the report view of Power Bi, we can start to create a matrix visual with our data. However, our visual is missing some key line items, is displaying negative numbers, is not in the correct order, and even contains some line items that are found in an income statement. In the next step, we will create an income statement that follows Generally Accepted Accounting Principles (GAAP).

Step 3: Build the Income Statement

To get our financial statement to take the shape of an income statement, we can use a Custom Headers Table in Excel. This table lists not only all line items in the correct order but also contains a list of which financial statement the lines belong to and a measure name for each line item.

Once imported, the headers table will be added to our data model as an additional dimension table called ‘DimHeaders’. However, as you can see below, we are still missing line items… Why is this? Because the missing information is nowhere to be found in the raw data. Instead, it must be calculated.

Think about how accounting works. Nowhere in the general ledger will we find Gross Profit. Why? Because Gross Profit is just the subtotal of Revenue and Cost of Sales. And EBIT is the subtotal of Gross Profit and Operating Expenses. And so on, until we get to Net Income. Percentages will also not be found in the general ledger and must be derived by some form of division. How can we tell Power Bi which categories are subtotals and which are percentages? From the headers table we just uploaded. We can fill in the missing line items by creating DAX measures under our fact table and adding them to our matrix visual. For example, to find Gross Profit percentage we would use this measure:

Once all of our DAX measures have been created and all of our line items have been added, we have completed our income statement:

Step 4: Create a High Level Financial Report Using Our Income Statement

Before wrapping up our project, let’s use the information from our income statement to create some more visuals that might be helpful for the retail company. Let’s add a couple of financial ratios to our report along with a graph showing monthly revenue and gross profit percentage by date. Also, lets add a visual that shows the last time we updated the chart, which we mentioned our company might like in Step 1. Lastly, we will create a visual showing revenue and expenses from our table.

Gross Margin Ratio is the amount of money a company retains after incurring the direct costs associated with producing the goods it sells and the services it provides. The higher the gross margin, the more capital a company retains. The operating margin measures how much profit a company makes on a dollar of sales after paying for variable costs of production, such as wages and raw materials, but before paying interest or tax. Each of these were created using DAX functions.

Our report refresh date was brought into Power Bi when we first imported our source data. This information doesn’t need to be attached to any other table in our data model and can be displayed as a card visual.

Our income statement shows the revenue per year. However, our company might want a closer look at which months they generate the most revenue. We can use Power Bi’s line and stacked column chart visual to show this information. The line in this case will be gross profit percentage by date.

Finally, let’s create a visual representation of where our company’s revenue is going. This is essentially the income statement in visual form. We can use Power Bi’s waterfall chart visual to do this.

Once all of these are added, we finally have our completed high level financial report to show to our retail business. Below is a screenshot of the final report and the final entity relationship diagram (ERD) of our data model.