Month over Month Calculation in Power BI using DAX

month over month calculation in Power BI using DAX

When working with dates, one of the common types of analysis is period vs period, such as Year over year, and Month over month. In this article and video, I’ll explain how you can use DAX to write calculations for month-over-month simply in any Power BI report.

Video

What is a month-over-month calculation?

Month over month, means comparing the value of each month with the value of the month before. An example is below;

Month over month calculation

This calculation can be done using many different ways in Power BI, most of them using DAX. You can use different functions to achieve the result. I am just showing one of the ways using ParallelPeriod function.

ParallelPeriod function in DAX

ParallelPeriod is a tabular function, that returns a table of dates that is parallel period to the current period. You can select what the period should be (internal) and the number of it back or forth. Here is how the function can be used;

PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)  

Here are how parameters work;

  • dates: the Date column that slices and dices the visual
  • number_of_intervals: How many periods you want to go back (negative number) or forward (positive number)
  • interval: the period

This function returns a table of dates, and cannot be used directly in a measure, you can wrap it in other functions to return a scalar value for a measure.

The date field is the most important parameter here. This should be the date field from the date table, which can be the date field in either a custom date table or the default date table of Power BI. Let’s look at them one by one.

Default date table sample of Sales last month

If you have a date field in your table, and you have not turned off the auto-date/time in Power BI, that most probably means you have a built-in default date table created by Power BI, which you can use the field from it to calculate the sales last month like below;

Reseller Sales last month = 
CALCULATE(
    [Reseller Sales],
    PARALLELPERIOD(
        FactResellerSales[OrderDate].[Date],
        -1,
        MONTH)
)

The [OrderDate].[Date] is representative of the date field in the default date table. The .[Date] part of this is important because otherwise, you are not using the date field of that table. If you want to learn more about the default date table, read my article here. Here are the results of the expression above:

Sales last month calculation in Power BI

The interval is Month, which means we are getting the sales of a month. That month is previous month, because the number of intervals is -1. and the date field should be the same field used as the Axis of the visual.

Custom date table sample of Sales previous month

If you are using a custom date table, you have to mark it as a date table in Power BI, and then you can use the date field directly in the ParallelPeriod without the “.[Date]” part.

Internet Sales last Month = 
CALCULATE(
    [Internet Sales],
    PARALLELPERIOD(
        DimDate[FullDateAlternateKey],
        -1,
        MONTH)
)

The expression above can return the same result for previous month’s calculation:

Sales last month calculation in Power BI using a custom date table

Month over Month variance

Once you got the calculation of previous month, the month over month variance is just a subtract.

Internet Sales MoM Var = [Internet Sales]-[Internet Sales last Month]

Month over Month percentage (growth or shrinking)

And the percentage would be another simple calculation like below:

Internet Sales MoM % = DIVIDE([Internet Sales MoM Var],[Internet Sales last Month])

Here is the results with some conditional formatting added;

Month over month variance and percentage in Power BI

Other intervals and periods are possible

ParallelPeriod gives you the option to change the interval to Quarter or Year too, and you can change the number of intervals to more and change it to negative and positive. There are other functions that can be used for this type of calculation, DateAdd is one of them. Read my blog here to understand the difference of ParallelPeriod and DateAdd;

Download Sample Power BI File

Download the sample Power BI report here:

    Enter Your Email to download the file (required)

    Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
    Reza Rad
    Trainer, Consultant, Mentor
    Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
    His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
    He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
    He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
    Reza’s passion is to help you find the best data solution, he is Data enthusiast.

    Leave a Reply