One of the most common tasks in data analytics (sales, CRM, logistics – it doesn’t matter) is to compare some absurd important metric with itself in the past year or previous time period.
In Power BI, this is usually solved easily and naturally with the help of time intelligence functions. Or, if a person is inclined to complicate his own life, or he/she is too lazy to create a table of dates, or his/her company lives on the lunar calendar (seriously, dozens of companies in Fortune 500 list uses 13-period fiscal year) – this is solved with the help of CALCULATE and a bunch of filters.
I want to devote this brief article to one of the special cases of such a comparison. This is a comparison of the selected period to a similar previous one. For example, we chose the period September-December, and we want to compare it with the data for April-August. And not just compare, but also beautifully reflected on the chart.
This question was asked in one good Power BI Telegram chat. And without hesitation, I sketched a quick example in 10 minutes. Later, I found an old article by awesome Reza Rad, with a solution to the same problem. Well, okay. I have a slightly different idea and easier formulas 🙂
If you are in a hurry, you can immediately steal the finished file here
In general, we have such a simple model.
‘Dt_Replica’ is an exact copy of the ‘Dt’ date table. It is not linked to either a fact table or a date table. A very useful feature in many scenarios.
We create a measure that will calculate the amount of [Val] for us:
SumVal =
SUM('Fact'[Val])
Great. We throw it and the dates onto the canvas and get the graph. After that, add a slicer:
Cool. Now, how can we add the same number of previous periods? There is no answer – slicer will simply not let us do this. And here a duplicated date table comes in. Change the field in the slicer to Month from ‘Dt_Replica’. At the output, we get such nonsense:
Since the axis on the graph and the slicer are not connected, we see everything that we have in the fact table. We need a special formula to connect two fields with the names of the months. Here is one, for example:
SumValCurr =
var stm = MIN(Dt_Replica[MonthID])
var fim = MAX(Dt_Replica[MonthID])
return
CALCULATE(
[SumVal],
Dt[MonthID] >= stm && Dt[MonthID] <= fim
)
Since the monthly breakdown was mentioned in the task, I use the month’s ID (number in order in the date table, see the model file). However, no one bothers you with using quarter id, date id, or just date; do you remember that all dates are integers?
What have we done? We took the minimum and maximum month ids selected in the slicer and filtered out a similar field in the date table. Easy? Yes!
With the same logic, we create a measure that will give us the amount for the previous months:
SumValPrev =
var stm = MIN(Dt_Replica[MonthID])
var fim = MAX(Dt_Replica[MonthID])
var diff = fim - stm + 1
return
CALCULATE(
[SumVal],
Dt[MonthID] >= stm - diff && Dt[MonthID] <= fim - diff
)
The only complication is that we calculate the duration of the selected period in the diff variable. Pure arithmetic, nothing more.
Now we throw all this created beauty onto the canvas – and voila:
Yes, one caveat. Formulas, as you probably already guessed, don’t care how did you selected the months – in a row or not. I think in the vast majority of cases this is not a problem, but keep this in mind.
So, we have solved the problem! However, my inner perfectionist is unhappy. The columns are narrow, there is a hole in the middle of the chart, the labels of the bars are shifted… Not good!
Let’s add another formula:
SumValPrev+Curr =
var stm = MIN(Dt_Replica[MonthID])
var fim = MAX(Dt_Replica[MonthID])
var diff = fim - stm + 1
return
CALCULATE(
[SumVal],
Dt[MonthID] >= stm - diff && Dt[MonthID] <= fim
)
We create another visual, throw dates, and a new measure on it. We also should build another formula for conditional formatting and apply it to the color of the chart:
CFCurr =
var stm = MIN(Dt_Replica[MonthID])
var fim = MAX(Dt_Replica[MonthID])
var diff = fim - stm + 1
var currmonid = SELECTEDVALUE(Dt[MonthID])
return
IF(
currmonid >= stm && currmonid <= fim,
1,
0
)
My final dashboard looks like this. I think you did even better:
Looks fine, doesn’t it?