With Power BI, how to calculate the previous month value using DAX formulas?
Managers, Accountants, Financial advisors and Business consultants use Power BI and month comparisons extensively, like the previous month values and the last year values. To create these comparison measures, use the DAX time intelligence functions available in the DAX editor. Let’s use the PREVIOUS MONTH DAX function to address this.
How to calculate the previous month value with Power BI?
Let’s consider the sales table with sales stored by month, by year and the customer number. Please note that for any time intelligence function DAX, used in Power BI or a Tabular model, a date column must be used as a reference. Let’s consider you have these two columns available in your data model:
- The Sales Amount column stores the number of sales registered on that day.
- The DateKey time column is a date used a the key for Power BI joins.
Check also the possibility to store the total of the sales on one unique day, like the 31st of January 2020 for example. Indeed, the goal is to introduce a unique date key for the time DAX functions, to enable the previous month sales calculation. To create the measure, use the previous month DAX function, it’s official documentation and syntax are available on Microsoft website..
Calculate the previous month from a Power BI data source
In this first example, use Power BI Desktop and a Power BI file (pbix extension) to map the column from the data source. And in the Visual Studio development software for a Tabular Model.
Sales Last Month := CALCULATE(SUM('ShopSales'[SalesAmount]), PREVIOUSMONTH('Time'[DateKey]))
Use two levels of formulas to calculate the previous month
This second approach is very similar to the first one, but it uses a measure already created. It’s done in two steps and uses two DAX calculations instead of one. For example, create the measures of the current month sales with this DAX formula.
Sales := SUM('ShopSales'[SalesAmount])
Then create the last month sales measure with this second formula:
Sales Last Month := CALCULATE([Sales], PREVIOUSMONTH('Time'[DateKey]))
In this Power BI tutorial, the two examples shows how easy it is to calculate a Power BI previous month value with the DAX native time functions. In this previous one on DAX functions, check out how to create a YTD calculation in DAX with Power BI.
