In this quick blog post I’m going to show you how to use a hidden DAX function to get a previous row value with just a few lines of code. It’s that hidden that intellisense doesn’t even recognise it! I’ll use an example of some fictional race results from some well-known cartoon characters!
Lets assume that in this scenario we wanted to analyse the time difference between 1st and 2nd place, 2nd and 3rd place etc in the race scenario below.
First we need to return [Time] as a scaler measure.
Max Time = MAX(‘Race Results'[Time])
We then need to use the hidden DAX Function “OFFSET” to gain access to the previous row value.
Previous Row = CALCULATE([Max Time],OFFSET(-1,ALL(‘Race Results’),ORDERBY(‘Race Results'[Time],ASC)))
*Note: You’ll get a red line under the term “OFFSET” and “ORDER BY”. This is just intellisense not recognising it as a valid function.
Finally, we can use the output of “Previous Row” measure in a DATEDIFF function to calculate the time difference in seconds between 1st and 2nd place, 2nd and 3rd place etc.
Time Gap Seconds = DATEDIFF([Max Time],[Previous Row],SECOND)+0
- Offset value (either positive or negative with any integer value i.e. -1,-2 or 1,2)
- A table or column reference i.e ALL(‘Race Results’)
- Order direction (asc or desc)
Hopefully this is another useful DAX function to add to your arsenal!
