Forum Discussion

Anon29's avatar
Anon29
Helper II
3 years ago
Solved

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...
Need Help
Show and Tell
  • danextian's avatar
    danextian
    3 years ago

    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.

danextian's avatar
danextian
Super User
3 years ago

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's avatar
Anon29
Helper II
3 years ago

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

  • danextian's avatar
    danextian
    Super User
    3 years ago

    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.