How to calculate last month value in DAX for Power BI?

How to calculate the last month value in DAX for Power BI? Managers, Financial advisors and Business Consultants uses intensively month comparisons. To create these comparison measures, use the DAX time intelligence functions available in the DAX editor. Let’s use the PREVIOUSMONTH DAX function to address this.

How to calculate the last month value in DAX for 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 reference.

Let’s consider you have these two columns available in your data model:

  • The SalesAmount column that stores the amount of sales registered on that day.
  • The DateKey time column is a date.

Check also the possibility to stored 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 PREVIOUSMONTH DAX function, and the documentation is available here.

This DAX example calculate directly the current month column from a DAX data source

In this first example, use Power BI Desktop 2.0 and a Power BI file (pbix extension) to map directly the column from the data source. And in the Visual Studio developer software for a Tabular Model.

Sales Last Month := CALCULATE(SUM('ShopSales'[SalesAmount]), PREVIOUSMONTH('Time'[DateKey]))

Second example with two level 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.

Be the first to comment

Leave a Reply

Your email address will not be published.


*