Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abraatz
New Member

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
v-huizhn-msft
Employee
Employee

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

abraatz
New Member

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.