cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abraatz Frequent Visitor
Frequent Visitor

Lookup value related to previous date in table

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.

2 REPLIES 2
abraatz Frequent Visitor
Frequent Visitor

Create column of values from next row in query editor

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 Super Contributor
Super Contributor

Re: Lookup value related to previous date in table

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.

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

Thanks,
Angelia