Get previous row value

[Anon29]

Hi all, I am trying to create a new column, (unsure to do it in Power Query editor or Dax) to show the previous value based on 2 conditions.

I want add a column called "Previous Purchase". It has to be for the same Customer ID. So below we have Sally Smith purchasing a Soft Toy at 4:56pm, so I want to show the previous purchase was a Barbie, and so on till its a different Customer ID. The time column is not always sorted in order. I'd like to avoid sort if possible as it tends to slow down my refreshes in power query.

After this I will apply a filter on the irrelevant Previous Purchase products.

This is what my Data looks like this below:

[danextian · Accepted solution]

Hi @Anon29 ,

I would assume that the date has to be considered in the calculation  as well and not just the time. If so,  create a datetime column first. If those two columns are in the proper date type, you should be able to add both.

datetime =
'table'[date] + 'table'[time]

then this column to acccess the previous record that is preceding the current time and which is equal to the current row customer

Previous Purchase =
CALCULATE (
    LASTNONBLANK ( 'table'[product], 1 ),
    FILTER (
        FILTER ( 'table', 'table'[customer id] = EARLIER ( 'table'[customer id] ) ),
        'table'[datetime] < EARLIER ( 'table'[datetime] )
    )
)

If you don't need to consider the date, just keep the datetime calculated column and use 'table'[time instead] in the second calc column formula.

[Anon29]

Hi @danextian , can this also be achieved in the power query editor?

[danextian]

It can be but pls take note that DAX is more optimized at scanning a table than M. Depending on your source and the size of your table, this can be very slow to run.