How to calculate the last month value in DAX for Power BI? Managers, Financial advisors and Business consultants use month comparisons extensively. 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 last month value in DAX with Power BI desktop?
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 that stores the number of sales registered on that day.
- The DateKey time column is a date.
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..
This DAX example calculates the current month column from a DAX 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]))
The second example with two levels of DAX formulas to calculate the last 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 article, the example shows how easy it is to calculate a 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 for Power BI.