Custom KPI Trend Calculation – UP ↑ – DOWN ↓ – or About The Same → (PowerBI DAX Tutorial)

Imaging a scenario where your monthly sales are $80 while the budge value for sales was supposed to be $100. Obviously, you are not making your number. However, would you rather be 20% short but with sales trending up or 20% short but sales trending down? I think that most people would agree that the up or down trend can provide important context to this sales variance. What is interesting, however, is the fact that how we might calculate this trend does not seem to be very well covered across various performance management blogs and channels which gives me an opportunity to offer my approach to this problem.

In this video, I talk about how we might implement a trend (up, down, about the same) calculation in Power BI. I suggest a fairly simple approach. We take our data points and divide them into first and second halves. Then we calculate an average sale for the first half as well as the average sale for the second half and then we compare the two. If the first half is greater then the second, then the slope is down. If the second half sales are higher than the first, then the trend is up, and when the sales are plus or minus 10% from each other I am assuming that the trend is about the same.

You can download the model and check my calculation here.

Trend = 
var SelectedWeekOptionsValue = SELECTEDVALUE(WeeksOptions[Weeks#])
var CurrentYearWeek = SELECTEDVALUE('Date'[YearWeek])
var LastNWeeksOfSales = 
FILTER(
	ADDCOLUMNS(
	    TOPN(
	        SelectedWeekOptionsValue, 
	        CALCULATETABLE(
	            VALUES('Date'[YearWeek]), 
	            'Date'[YearWeek]<CurrentYearWeek),
	        'Date'[YearWeek], 
	        DESC
	    ),
	    "tempSales",
	    [Sales]
	),
	[tempSales]<>BLANK()
)

var WeeksInRange = 
COUNTROWS(
	SUMMARIZE(
		LastNWeeksOfSales,
		[YearWeek]
	)
)


var FirstHalfAvg=
AVERAGEX(
	TOPN(
		INT(WeeksInRange/2),
		LastNWeeksOfSales,
		[YearWeek],
		ASC
	),
	[tempSales]
)

var SecondHalfAvg=
AVERAGEX(
	TOPN(
		INT(WeeksInRange/2),
		LastNWeeksOfSales,
		[YearWeek],
		DESC
	),
	[tempSales]
)

var growthRate = DIVIDE(FirstHalfAvg, SecondHalfAvg)

return if(growthRate>1.1, "↓", if(growthRate<0.9, "↑", "→" ))