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


The problem

Sometimes you want to have information from “the previous row” in your table. The 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:

Sales amount per day
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:

Previous Date of Sales = 
MAXX(
    ADDCOLUMNS(
        SUMMARIZE(
            'FactInternetSales'
            ,'Product Name'[EnglishProductName]
            ,'FactInternetSales'[OrderDate]
        )
        ,"@PreviousDate"
        , CALCULATE(
            MAX('FactInternetSales'[OrderDate])
            ,FILTER(
               ALL('FactInternetSales'[OrderDate])
                ,'FactInternetSales'[OrderDate] < EARLIER('FactInternetSales'[OrderDate])
            )
        )
    )
    ,[@PreviousDate]
)

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.

With the measure added, you can see the previous date of sales of the selected item.
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.

Value of Previous Date Sales =
VAR PreviousDate = [Previous Date of Sales]
RETURN
CALCULATE(
  [Sum Amount]
  ,'FactInternetSales'[OrderDate] = PreviousDate
)
Days since last sale =
DATEDIFF(
  [Previous Date of Sales]
  , MIN(FactInternetSales[OrderDate])
  , DAY
)

Hopefully, this will help in your DAX adventures!

Take care!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close