How to create a calculation of the previous month value using DAX formulas in Power BI?
Discover how to calculate the Power BI previous month value in this tutorial using 2 different approach’s. Managers, Accountants, Financial advisors and Business Intelligence 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.
Table of Contents
1. Use PREVIOUSMONTH function in DAX to get the previous month
The DAX PREVIOUSMONTH function goal is to retrieve data from the immediate prior month based on a specified date column. For instance, if you have sales data and want to compare this month’s sales to last month’s, you could use PREVIOUSMONTH(‘Table'[Date]) within a measure.
When applied, the function filters the data to only include values from the month immediately preceding the latest date in the column. It’s vital to note that the function operates within the context of your Power BI data model. In other words, it works best when you have a proper date table related to your transactional data. I would add that I highly recommended to double check the configuration of your table marked as date.
Two options available using DAX formulas for this calculation
Let’s consider the sales table with sales stored by month, by year and the customer number. Also, for any time intelligence function in DAX, i.e. in Power BI or a in Tabular model, a date column must be the 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 sales of one month on one unique day. Like the 31st of January 2020 for the January month for instance. 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.
One prerequisite: set up a Date Table to use time intelligence functions in Power BI
To effectively use time intelligence functions in Power BI, one prerequisite is setting up a Date Table. This table, featuring a continuous range of dates, is crucial for conducting accurate time-based analyses, as it enables Power BI to apply dynamic calculations over various periods, ensuring precision and consistency in temporal data evaluation.
Sometime, when building a model, the following error can be encountered: “Failed to resolve name ‘REVIOUSMONTH’. It is not a valid table, variable, or function name.” To fix it, double check the formula bar and the highlighted errors, it usually syntax errors, or missing parenthesis, i.e. classical development errors. And the messages displayed by Power BI are explicit.
2. Calculate previous month directly from Power BI data source
In this first example, use Power BI Desktop and a Power BI file (pbix extension) to map directly the column from the data source. And in the Visual Studio development software for a Tabular Model using SSAS.
Sales Last Month =
CALCULATE (
SUM ( sales[sales] ),
PREVIOUSMONTH ( 'time'[date_key]),
REMOVEFILTERS ( sales )
)
And the result in the Power BI report gives something like this:
3. Use two levels of formulas to get the previous month
This second approach is very similar to the first one, but it simply 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('sales'[SalesAmount])
Then create the last month sales measure using this second DAX formula below:
Sales Last Month =
CALCULATE (
sales[sales],
PREVIOUSMONTH ( 'time'[date_key]),
REMOVEFILTERS ( sales )
)
About previous month calculation in Power BI
In this Power BI tutorial, the two examples shows how easy it is to calculate a Power BI previous month value using the DAX PREVIOUSMONTH time function. In this previous tutorial on DAX functions, check out how to create a YTD calculation using DAX and Power BI.
