Search code examples
sql-serverstatisticspowerbidaxwindow-functions

PowerBI Rate Of Change

I have a dataset that looks like this:

Date, Value
01/01/2020 13:00, 2
01/01/2020 14:00, 3
01/01/2020 15:00, 4
01/01/2020 16:00, 9
01/01/2020 17:00, 2

I want to be able to have a measure created in PowerBI that looks at the Percent of Change from one to the other. Ideally, it would be great if I could get the Max, Average, and Min Rate of Change. Looking around, it seems like you would have to "loop" over the data to do this, and Looping in DAX is not really a thing.

Any help would be greatly appreciated.

PS. We are querying the data live, so the idea of creating separate "temp" tables really isn't an option.

Solution

  • Assuming you only have one Value for each Date and that there are no duplicate Date values, you can get the rate of change between just one Date value in your filter context (the most recent in the example below) and the Date value before it doing something like this:

    RateOfChange = 
var cd = calculate(max(YourTable[Date]))
var cv = calculate(max(YourTable[Value]),YourTable[Date] = cd)
var pd = calculate(max(YourTable[Date]),YourTable[Date] < cd)
var pv = calculate(max(YourTable[Value]),YourTable[Date] = pd)
return
divide(cv - pv, pv, 0)

    1

    Though as you can see, it isn't exactly the most elegant or efficient at scale nor will it provide you the min, average and max change across the dataset. Ideally you would be importing this data and generating a calculated column to hold the Rate of Change value within each row. That way you would be able to get a min, average and max.

    If you absolutely have to resort to direct query, you should look into whether you can either add some tables to report out of or at the least some views that help you get the data into the format you require for the reporting.