Power BI Blog: Calculating Monthly % Change
13 May 2021
Welcome back to this week’s edition of the Power BI blog series. This week, Jonathan Liau will look at how to write a measure to compare the previous month’s collection values to the current month’s collection values.
Last week, we looked at how to use the built-in Gauge visualisation, where we used a measure that would compare between the earlier monthly collection values to the current monthly values.
This week, we thought we should go over how we created that measure, since it may be useful in other applications.
The DAX code for the Monthly % Change measure is as follows:
Monthly % Change =
VAR MonthFilter = SELECTEDVALUE(CalendarTable[Month], 1) - 1
VAR LastMonthAmt =
CALCULATE([Total Ton],
FILTER(
ALL(CalendarTable),
CalendarTable[Month] = MonthFilter
)
)
VAR Result =
([Total Ton] / LastMonthAmt) - 1
Return
Result
Let me explain it. We begin with a variable on line two [2]:
VAR MonthFilter = SELECTEDVALUE(CalendarTable[Month], 1) – 1
This declares a variable as MonthFilter and assigns variable the value of the result of the following evaluation:
SELECTEDVALUE(CalendarTable[Month], 1) – 1
The SELECTEDVALUE function will return with the value of the current month selected on the slicer. We then adjust this value by ‘-1’ so that we obtain the value of the previous month.
The next step, starting from line four [4] is to create another variable. This variable is called LastMonthAmt, ‘Amt’ short for amount, to calculate the tonnage collected in the previous month,
VAR LastMonthAmt =
CALCULATE([Total Ton],
FILTER(
ALL(CalendarTable),
CalendarTable[Month] = MonthFilter
)
)
This variable will calculate the [Total Ton] collected in the previous month and store that value.
We can then calculate the result, in line 12, in a variable called Result.
VAR Result =
([Total Ton] / LastMonthAmt) - 1
In this variable we calculate the percentage difference by dividing the [Total Ton] by the variable LastMonthAmt then subtracting one [1] from that value to get the percentage.
The final step here is to return with the result:
Return
Result
This measure also works on a line visualisation where we can see the percentage change over time.
That is, it for this week!
In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.
