Upcoming webinar on 'Inforiver Charts : The fastest way to deliver stories in Power BI', Aug 29th , Monday, 10.30 AM CST.    Register Now

Excel-like MoM, QoQ, YoY % Calculations in Rows in Power BI

by Inforiver | Aug 30, 2022 |

Today we are going to take a quick look at how to calculate Month over Month (MoM), Quarter over Quarter (QoQ) and Year over Year (YoY) growth in Power BI, just as easy as you can do in Excel.

We have a monthly P&L dataset in Excel starting Jan 2019 going all the way till Dec 2021, totaling 36 months in columns. The first few columns are shown here.

Sample financial statement

If we need to calculate MoM, QoQ and YoY growth in Excel, for each month, we start adding additional rows and type in the formulae. Here is one way to do it.

MoM QoQ YoY Excel calculation

In essence, we have inserted 6 rows in the table:

  1. Sales PM (Prior Month)
  2. Sales MoM % (Month over Month)
  3. Sales SPLQ (Same Period Last Quarter)
  4. Sales QoQ % (Quarter over Quarter)
  5. Sales SPLY (Same Period Last Year)
  6. Sales YoY % (Year over Year)

Notice that the Sales PM, Sales SPLQ and Sales SPLY metrics just shift the original Sales record across time - respectively by 1, 3 and 12 months.

Let’s look at how we can do this in Power BI.

***

1. Using Native Matrix Visual

When we create a DAX measure for tracking YoY, QoQ and MoM changes, we run into some issues. To begin with, DAX measures are rendered as columns, and not as a row. The second issue is that the measure is calculated for all the financial accounts – not just Net Revenue.

MoM Calculation PowerBI Matrix

When you turn on the ‘Switch values to rows’ property of the matrix, things get worse as the records simply double. The GL account rows (e.g., Net Revenue, Cost of Goods Sold) become empty too, as the values shift to the measure groups beneath them.

MoM Calculation PowerBI Show Value in Rows

Unfortunately, neither of these formats will suit us.

2. Using Inforiver for Power BI

With Inforiver, you can insert calculated rows like how you are used to doing in Excel.

Here we have the account statement recreated in Power BI using Inforiver. All values in the table are scaled to millions.

Power BI base report

We’ll now explore how to insert MoM, QoQ and YoY calculations in additional rows.

To do this, we leverage Inforiver’s powerful formula engine which you can use to insert rows and columns. In the walkthrough below, we will restrict our focus to the formulae used after you have inserted each row.

1. Month over Month Sales (MoM %)

To compute Sales Prior Month (Sales PM), we need to shift Sales record by a month to the right. This is accomplished by using the function RELATIVE_COLUMN(),  to which we pass the offset amount of -1, signifying the position of the prior period relative to the current period.

Check out the formula below.

The [Gross Profit].[Sales] indicates that the Sales account is part of the Gross Profit hierarchy.

PowerBI Prior Month Sales

Once we calculate the above, the MoM % change can be calculated as follows. You can format the negative values in red by using the formatting options in the toolbar.

MoM % Sales Growth PowerBI

Note that the IF condition is used to ensure that the Jan 2019 value does not throw an error due to division by Sales PM when it is empty.

2. Quarter over Quarter Sales (QoQ %)

Calculating Quarter over Quarter sales is very similar, except that we need to shift time period by three (3) months. You can observe that the Jan sales value of 448 is now populated in April.

Sales Same Period Last Quarter SPLQ PowerBI

The QoQ % can be calculated easily now.

QoQ % Sales Growth PowerBI

3. Year over Year Sales (YoY%)

For YoY calculations, we first shift the time period by 12 months into the past. You can notice that the Jan 2019 sales value is now listed against Jan 2020 sales in the Sales SPLY row.

Sales Same Period Last Year SPLY PowerBI

The YoY Sales growth in percentage can be now calculated easily.

YoY % Sales Growth PowerBI

***

In case you want only the YoY, QoQ and MoM rows, you can just hide Sales PM, Sales SPLQ and Sales SPLY rows as you do with Excel.

Only MoM QoQ YoY Sales PowerBI

You can even minimize the emphasis and reformat the report.

Formatted MoM QoQ YoY Sales PowerBI

***

Overall, the ability to perform time shifting calculations in rows opens big possibilities in Power BI, especially for Excel fans. Check out the Twenty (20) Excel features that Inforiver delivers in Power BI.

Inforiver Matrix can be purchased directly from Microsoft AppSource.


Inforiver Logo
IBCS Certified
Power BI certified
About Inforiver!

Inforiver drives business productivity and business performance with faster time to insights inside Power BI through an intuitive no-code experience. The product is developed by Lumel Technologies (formerly Visual BI), which has a decade of experience in building add-on solutions on top of BI platforms (SAP BI and Power BI) with our suite of innovative products such as ValQ, xViz, and BI Hub
Headquarters
5920 Windhaven Pkwy, #130
Plano TX 75093
ValQ
xViz
BI Hub
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram