Calculate last month value in DAX with Power BI

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.

7 Comments

  1. I just wanted to say thank you. I’ve spent my entire day trying to do exactly this. I’ve gone to at least 50 other forums and tried 50 other things and this is finally what worked

    • Hi Muhammad, for the DAX formula to work, the time dimension used must be flagged as Time Table in Power BI.
      To do so, the time table must have contiguous dates (no gaps) at day level. For exemple if you consider 2019 and 2020, all dates must be present, and unique.
      Did you manage to make it work?

  2. Current Headcount = CALCULATE(SUM(‘Edge HSE KPI Data'[Head Count Totals]),
    PREVIOUSMONTH(‘Calendar'[Date].[Date]))

    When I display the value on a table, card or chart it is just blank.
    My Date Column is continuous from start of data to end of data.
    It has been linked with a relationship to the data table.
    Can your see anything wrong?

  3. I have tried too, does not work, it returns December data instead of January. The current month in my dada table is February.

  4. Expert Only.

    you are really Expert.

    You saved My Day I spent 2 days for This logic .

    A Big Big Thank You

Leave a Reply

Your email address will not be published.


*