Creating an Income Statement with DAX in Power BI – Part 2

This is Part 2 in developing an Income Statement in Power BI. Part 1 can be found here. The example files used for this post can be found here.

So.. We left off part 1 with our Income Statement report looking like this:

I had created a measure to populate the matrix visual. It uses =SWITCH() to return the YTD amount for the revenue and expense row headers and returns a subtotal for the net income header.

To finish building the income statement, I needed to add two more line items: retained income from the beginning and end of the year. I had already created the categories in the Power Query Editor, so I had to update the DAX statement to return logic for these lines.

I started with the End of Year Retained Income. For our example scenario, this totals the net income from the current and previous years. This meant I couldn’t use the YTD Subtotal measure, as I needed to return data from previous years as well.

My first step was to create an All Subtotals measure. This measure returns the cumulative total for all previous periods, not just the current year (as YTD Subtotals does). There are two changes from the YTD Subtotals measure:

• it calculates the Total Amount measure rather than YTD Amount
• added filter statement of FILTER(ALL('Calendar'[Date]), 'Calendar'[Date] <= MAX('Calendar'[Date]))

I added this measure into the Income Statement DAX measure. I created an additional line item for the End of Year Retained Income (line 5) with consistent logic for the net income line item above.

I dropped this into the visual.. and it looked good! The retained income from previous years was carrying forward and accumulating properly every year.

The final step was to add the line item for retained income at the start of year. This makes the income statement easier to read and see how the current year net income changes overall retained income.

In this example scenario, the start of year retained income should display the end of year retained income from the previous year. To achieve this I wrapped the All Subtotals measure in a =CALCULATE() statement and filtered by using the =PREVIOUSYEAR() function.

I dropped the updated measure into the visual. This is a succinct example for an income statement, but the logic can be expanded upon for more complex scenarios.

That was part two of creating an Income Statement in Power BI! I created a new DAX measure, All Subtotals, to create a cumulative total year over year. I also added some more logic and complexity to the =SWITCH() measure I had created in part 1.

The next post in the series will cover how to create a Balance Sheet!