Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

add previous row values

Hi,   I have a fact table of sales and I need to get 2 new columns, wich will display Previous date of purchase and Previous sales status (both red in the table below).   I'm trying to get same r...
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi Anonymous,

     

    You can create calculated columns follow below DAX:

     

    previous date of purchase = CALCULATE(MAX('Table1'[date of purchase]),FILTER(Table1,'Table1'[month of sale]=EARLIER(Table1[month of sale]) && 'Table1'[customer]=EARLIER(Table1[customer])&&'Table1'[product]=EARLIER(Table1[product])&&'Table1'[date of purchase]<EARLIER('Table1'[date of purchase])))

     

    previous sales status = LOOKUPVALUE( Table1[sales status], Table1[month of sale], Table1[month of sale], Table1[customer], Table1[customer], Table1[product], Table1[product], Table1[date of purchase], Table1[previous date of purchase] )

     

     

    Best Regards,
    Qiuyun Yu

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

Hi Anonymous,

 

You can create calculated columns follow below DAX:

 

previous date of purchase = CALCULATE(MAX('Table1'[date of purchase]),FILTER(Table1,'Table1'[month of sale]=EARLIER(Table1[month of sale]) && 'Table1'[customer]=EARLIER(Table1[customer])&&'Table1'[product]=EARLIER(Table1[product])&&'Table1'[date of purchase]<EARLIER('Table1'[date of purchase])))

 

previous sales status = LOOKUPVALUE( Table1[sales status], Table1[month of sale], Table1[month of sale], Table1[customer], Table1[customer], Table1[product], Table1[product], Table1[date of purchase], Table1[previous date of purchase] )

 

 

Best Regards,
Qiuyun Yu

Noelia's avatar
Noelia
Frequent Visitor
8 years ago

Is there a way to do this with a measure instead of a column? My table has too many rows and there is not enough memory to solve it with a calculated column.