Find a Value in the Previous Row

Discover the latest Power BI courses

By Alison Box

DAX Power BI in Practice

Recently I was asked by one of our clients to solve this question; how could they calculate the number of days between customers' transactions? Clearly, in order to do this calculation, you would need to find the transaction date that was previous to the transaction date sitting in the current row. You could then subtract the earlier date from the later date and return the days between them.

It occurred to me, that such a calculation could have a more general application; not necessarily just calculating days between two transaction dates but it could be used to calculate any difference between values sitting in the current row and values in the previous row, for example the difference in quantity between two consecutive transactions.

Here's an example of the "Qty Difference" and "Days Difference" calculated columns I'm talking about:-

In the above example, I've filtered out CUSTOMER ID 7 and sorted on the SALE DATE column so you can see the calculations, but the sorting and filtering of the data in the table makes no difference to the outcome of the calculation.

In order to find the Previous Row to the current one we need to start by numbering the rows in ascending order based on the Sale Date. It would then be possible to find the previous row because it must have a number that is immediately less than the number sitting on the current row. To create this ascending number , I used Power Query to firstly sort the data by the SALE DATE column and then add an INDEX column:-

Once I had generated this Index column, I could use it to calculate the "Days Difference" column which calculates the days between the date in the previous row and the date of the current row for each customer. This is the DAX expression in the calculated column:-

DAYS DIFFERENCE =
VAR myindex = Sales[INDEX]
VAR mycustomer = Sales[CUSTOMER ID]
VAR previousindex =
    CALCULATE (
        MAX ( Sales[INDEX] ),
        FILTER ( Sales, Sales[CUSTOMER ID] = mycustomer && Sales[INDEX] < myindex )
    )
VAR previousdate =
    CALCULATE (
        MAX ( Sales[SALE DATE] ),
        FILTER (
            Sales,
            Sales[INDEX] = previousindex
                && Sales[CUSTOMER ID] = mycustomer
        )
    )
RETURN
    IF ( previousdate, Sales[SALE DATE] - previousdate )

Note that this calculation returns a column of a DATE data type so you will need to edit the data type to WHOLE NUMBER
In the "old days" we would've had to use the DAX function, EARLIER to find the values on the current row but now we can just use DAX Variables.

In this expression, the first two Variables, "myindex" and "mycustomer" find the Index number and the Customer ID sitting in the current row.

The "previousindex" variable finds the previous Index number from the current row. It does this by filtering the Sales table to match the customer on the current row and also any rows that have an Index number that is less than the Index of the current row. Of these filtered Index numbers, the MAX function finds the largest of these that is less than the Index number sitting in the current row i.e. the Index number previous to the Index number in the current row.

Then to find the date sitting in the previous row, the "previousdate" variable filters the row in the Sales table where the Index number is the same as "previousindex" and the customer is the same as "mycustomer".

Finally, we just subtract the date on the current row from the date sitting in the previous row. The IF function ensures there will be no calculation if there is no previous date i.e. this is the customer's first transaction.

So we now could use almost this same DAX expression to calculate the "Qty Difference". This calculated column finds the difference between the quantity on the previous row for each customer and the quantity on the current row:-

QTY DIFFERENCE =
VAR myindex = Sales[INDEX]
VAR mycustomer = Sales[CUSTOMER ID]
VAR previousindex =
    CALCULATE (
        MAX ( Sales[INDEX] ),
        FILTER ( Sales, Sales[CUSTOMER ID] = mycustomer && Sales[INDEX] < myindex )
    )
VAR previousqty =
    CALCULATE (
        MAX ( Sales[QTY] ),
        FILTER (
            Sales,
            Sales[INDEX] = previousindex
                && Sales[CUSTOMER ID] = mycustomer
        )
    )
RETURN
    IF ( previousqty, Sales[QTY] - previousqty )

So this is what I love about DAX. Once you find a solution to a specific calculation, you can often then use that DAX "pattern" to solve a number of other calculations.

Benefit from expert training

At Burningsuit we have years of experience in providing expert, well-rated training courses in Power BI, DAX and Excel. We're adept at providing the information and skills you need to make the most of Power BI together with your data and offer on-site and public courses across London and the UK.

To contact us, please call on 0800 0199 746 or use our Contact page. We're always happy to discuss Power BI training and implementation.

5 Comments

  1. Great article this was exactly what I was looking for!!! You saved me a lot of time!

    I’ve just updated your function and I’d like to share it with you:
    First of all in my salution the index must be 1 based (like you did)
    Here the DAX code:

    NetShortPositionPrevious =
    VAR myindex = FileEntries[Index]
    VAR filter1 = FileEntries[PositionHolder]
    VAR filter2 = FileEntries[ISIN]

    VAR previousindex =
    CALCULATE (
    MAX ( FileEntries[Index] ),
    FILTER ( FileEntries, FileEntries[PositionHolder] = filter1 && FileEntries[ISIN] = filter2 && FileEntries[Index] < myindex )
    )

    VAR previousNetShortPosition =
    IF (previousindex,
    CALCULATE (
    MAX ( FileEntries[NetShortPosition] ),
    FILTER (
    FileEntries,
    FileEntries[Index] = previousindex
    )
    )
    )
    RETURN previousNetShortPosition

    I left only index condition in the second filter and I placed an IF condition before in order to not to calculate anything if I haven't found any previous row.

  2. Hi, I am new to DAX but trying to adapt your scenario above. When I create my similar first two variables, I receive error: A single value for column name ‘Index” in table named “mytable’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation… Please help.

    • There are two different types of DAX expression; calculated columns and measures. You are trying to put the DAX expression into a measure when it should be put into a calculated column. You can’t normally use the same expression in a measure as you would use in a calcuated column. Use the “New Column” button on the Column Tools tab to create the DAX expressions outlined in the blog. Hope this helps.

  3. Hi,

    I am a little confused about the reason for adding the index. As far as I can see, this simpler function does the job:

    Days between =
    VAR currentDate = Query1[Day of buy].[Date]
    VAR currentThing = Query1[Thing]
    VAR previousDate =
    CALCULATE (
    MAX ( Query1[Day of buy] ),
    FILTER ( Query1, Query1[Thing] = currentThing && Query1[Day of buy] < currentDate )
    )
    RETURN
    IF ( previousdate, 1*(currentDate – previousdate) )

    Does the index just make it faster?

    • Hi Till,
      Your solution will work fine, provided you know that your dates all run consecutively and do not have any duplications If you have multiple transactions on one day or your dates are not sorted in order etc. it won’t work, that why we build an index, because we can guarantee that the index is concurrent and non-repeating.
      Hope this helps
      Stuart

Leave a Reply

Your email address will not be published. Required fields are marked *