Getting data from the previous row in your table visual in Power BI

Photo by Dennis Möller on Unsplash

The problem

Sometimes you want to have information from “the previous row” in your table. Problem is, your measures don’t have row context, so it doesn’t know on which row it is. Creating a column will solve this problem easily, but generally, it’s best practice to use measures. In this blog, I’ll explain what is needed to let a measure see the necessary row context for retrieving information from the previous row in a table visual.

Our table looks like this:

Salse amount per Order date for the AWC Logo Cap

And what I want to know is the number of days between those two rows. Therefore I need to know what the previous date was. To do this, I create a measure and will explain step by step what and why we’re doing this:

First of all, using SUMMARIZE(), we create a table that’s grouped by the distinct values from the columns as can be seen in our table visual on the first image. This way we create the necessary row context that looks the same as the filter context in our table visual.

The next part is adding a column where we are going to add the previous order date. With Max, we want to get the latest value. In our filter statement, we request all the order dates (ignoring all filters), where the order date is before the order date in the current filter context.

Showing the previous date of sales in the table visual.

Usage

Now the first, most important part, is done and it’s time to use our base measure in different kinds of calculations, for example, How many days were between the current sale and the previous one? Or: What was the value of the previous sale? We can now create very easy calculations using our Previous Date measure.

Hopefully, this will help in your DAX adventures!

Take care!