Lookup value related to previous date in table

[abraatz]

I was able to figure out how to do this using modeling/DAX to get to what I wanted, but I need to be able to do this using query editor when I'm pulling in data so it is quickly repeatable on many different queries, and those queries can then be appended together to form the final table.

What I want to do is create a new column of values using previous row's value, then calculate a return item in. The initial query returns columns including Date, Adj Close (with Adj Close being the column I want to pull from), with date values descending order (most recent first). In query editor a column was created to assign the Ticker to each row (all are the same).

Step 1: created a PrevDate column

PrevDate = CALCULATE(MAX(Query1[Date]), (FILTER(Query1,EARLIER(Query1[Date])>Query1[Date])))

Step 2:  created a PreAdjClose column

PreAdjClose = CALCULATE(SUM(Query1[Adj Close]), (FILTER(Query1, EARLIER(Query1[Ticker])=Query1[Ticker] && EARLIER(Query1[PrevDate])=Query1[Date])))

Step 3: created DayReturn column

DayReturn = IF(ISBLANK([PrevDate]), Blank(), Query1[Adj Close] / Query1[PreAdjClose] - 1)

Is it possible to recreate these steps in Query Editor? I do not necessarily need the PrevDate column, but did not know how to just retrieve the value from the Adj Close column's next row without that step.

[v-huizhn-msft]

Hi @abraatz,

You can use the List.Sum function to get the sum function. For if, you can click Add conditional column under Add column on Edit Query home like the following screenshot.


And I am not specific about Power Query, you can post it to Power Query forum to get professional support.

Thanks,
Angelia

[abraatz]

I was able to figure out how to do this using modeling/DAX to get to what I wanted, but I need to be able to do this using query editor when I'm pulling in data so it is quickly repeatable on many different queries, and those queries can then be appended together to form the final table.

What I want to do is create a new column of values using previous row's value, then calculate a return item in. The initial query returns columns including Date, Adj Close (with Adj Close being the column I want to pull from), with date values descending order (most recent first). In query editor a column was created to assign the Ticker to each row (all are the same).

Step 1: created a PrevDate column

PrevDate = CALCULATE(MAX(Query1[Date]), (FILTER(Query1,EARLIER(Query1[Date])>Query1[Date])))

Step 2:  created a PreAdjClose column

PreAdjClose = CALCULATE(SUM(Query1[Adj Close]), (FILTER(Query1, EARLIER(Query1[Ticker])=Query1[Ticker] && EARLIER(Query1[PrevDate])=Query1[Date])))

Step 3: created DayReturn column

DayReturn = IF(ISBLANK([PrevDate]), Blank(), Query1[Adj Close] / Query1[PreAdjClose] - 1)

Is it possible to recreate these steps in Query Editor? I do not necessarily need the PrevDate column, but did not know how to just retrieve the value from the Adj Close column's next row without that step.