Forum Discussion
Get previous row value
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:
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.
3 Replies
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.
