Power BI Blog: Calculating Monthly % Change Update
20 May 2021
Welcome back to this week’s edition of the Power BI blog series. This week, Jonathan Liau will look at improving the measure that compares the previous month’s collection values with the current month’s collection values.
Last week, we created a measure that would compare the Monthly% Change. We displayed it on a gauge chart as follows:
However, looking at the visualisation, if we select January the first month in the series we get the following result of “Infinity”:
That’s a pretty big change.
This is not correct as there is not an infinite increase compared to last month. In the current dataset, we do not have a value before January. Therefore, it is caused by the division of zero or null (blank). We should include a check in our measure to deal with this error.
Let’s return to part of the measure we used last week:
Monthly % Change =
VAR MonthFilter = SELECTEDVALUE(CalendarTable[Month], 1) - 1
VAR MF_Check = IF( MonthFilter = 0, 1, MonthFilter )
VAR LastMonthAmt =
CALCULATE([Total Ton],
FILTER(
ALL(CalendarTable),
CalendarTable[Month] = MF_Check
)
)
VAR Result =
[Total Ton] / LastMonthAmt
Return
Result - 1
We can include a new variable that will check if the MonthFilter variable = 0. If it does, it should return with one [1], otherwise it should evaluate to the MonthFilter:
VAR MF_Check = IF( MonthFilter = 0, 1, MonthFilter )
We then replace CalendarTable[Month] = MonthFilter with CalendarTable[Month] = MF_Check. This will replace the filter condition to include our check that we have put in place.
The DAX code will now look like this:
Monthly % Change =
VAR MonthFilter = SELECTEDVALUE(CalendarTable[Month], 1) - 1
VAR MF_Check = IF( MonthFilter = 0, 1, MonthFilter )
VAR LastMonthAmt =
CALCULATE([Total Ton],
FILTER(
ALL(CalendarTable),
CalendarTable[Month] = MF_Check
)
)
VAR Result =
[Total Ton] / LastMonthAmt
Return
Result - 1
The updated visualisation will not return with a result of zero [0] when we select January on the slicer:
It is always important to put checks in your measures so that they display correctly on your visualisations.
That’s 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.
