A run rate is a forecast based on current performance. For SaaS companies, this metric is interesting to compute as their future performance is highly influenced by the actual ARR of the company. For example, if a company sells yearly subscriptions, the sales “seasonality” will be influenced by contract anniversary.
Business intent
The purpose of this article is to show how to compute a forecasting for the current quarter, based on performance of the same quarter during the last 2 fiscal years. The approach is quite simple. Let’s illustrate our business intent with an example. We want to forecast the sales for the current quarter, based on 5 weeks of data and 2 years’ history. We just started week 5 of the quarter.
- TFQ = This Fiscal Quarter
- Cum. = Cumulative
- SPLY = Same Period Last Year
- SP2FYA = Same Period 2 Fiscal Years Ago
- SPP2FY = Same Period Previous 2 Fiscal Years
- % GT = Percent Grand Total
So, at the end of week 4, we forecast ending the quarter at 1,496 k€, because our weekly cumulative performance for the same quarter for the same period the last 2 fiscal quarters represented 27.4% of the grand total. If we divide our current cumulative performance by the % GT SPP2FY, we get 1,496 k€.
Preparing a data model
We’ll reproduce this Excel table with Power BI, using a sample SQL database AdventureWorks. Our data model has only 2 tables. I created the date table with the DAX Template from SQLBI, because if offers much more possibilities than the date table available from AdventureWorks. Indeed, we’ll need columns such as the relative position of the current quarter.
First, I created a measure to store the sum of sales amount.
Sum Sales Amount = CALCULATE( Sum(FactInternetSales[SalesAmount]) )
Then I created 2 calculated columns on the date table to have the week number for each quarter. Firstly, I created “FW Day Of Quarter”, which ranks days of each quarter.
FW Day Of Quarter Number = ‘Date'[Date] – [Fiscal StartOfQuarter] + 1
From this field, I created “FW WeekOfQuarterNumber”, which create a week number out of the field above.
FW WeekOfQuarterNumber = INT ( CEILING ( ‘Date'[FW Day Of Quarter Number] / 7, 1 ) )
Creating the run rate measure
Lastly, I created the whole “Forecast” measure, step by step, based on the Excel table above.
I copy pasted this measure 6 times to create a table in my Power BI page, to give some context to the forecast measure. Only the name of the measure and the RETURN line are different.
The SQL database AdventureWorks isn’t the best to show this formula. If we look at the full quarter, here’s what it shows.
I’m stuck at Forecast formula selectedquarteroffset. what is fiscalrelativequarterpos? how do i calculate it?
It’s just a column on the date table that I’m referencing.
If you want to know more about the date table, I suggest reading this : https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/