Creating column to show previous row

[Anonymous]

Hi All,

I want to create a column that will provide me with the previous row. For example the second cell in the column would be the date 6/2/2019 5:39:58PM and so on. 

Thank you

[zoloturu]

Hi @Anonymous ,

To do that you need to modify PowerQuery code under your query.

Step1. Add an index column (which starts from zero). 

Step2. Add a custom column with next code (just copy and paste it)

let 
    CurrentIndex = [Index],
    PreviousIndex = CurrentIndex - 1,
    PreviousValue = try #"Added Index"[Date]{PreviousIndex} otherwise null 
in
    PreviousValue

(Where 'Added Index' the name of a last step for the moment in your query. If you have English Power BI Desktop then it will be the same, no need to change. And [Date] - your date column. Other code are variables.

Look

Result will be like next:

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

[Anonymous]

I am getting "null" in each column after pasting that as a query in query editor

[Anonymous]

Not sure if the other columns I have on the same table are interferring with the query?

[zoloturu]

@Anonymous ,

Can you post your PowerQuery query?

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer

[Anonymous]

This is the query I used.

I tried deleting all of the other columns in the table except for the Date and Index column and it worked. For some reason the other columns are causing the "null" in the custom column.

[v-kelly-msft]

Hi @Anonymous ，

As advised by @zoloturu ,you should first go to "query editor", and create a "index"column, the steps are as below:

1."Edit queries"->"Add column"->"Index Column"->from "1"

After that,you will see:

Then go back to data view and create a calculated column as below:

Column =
CALCULATE (
    MAX ( 'Table'[Date] ),
    Filter('Table','Table'[Index]
        = EARLIER ( 'Table'[Index] ) - 1)
)

Finally you will see:

Best Regards,

Kelly

[zoloturu]

Hi @Anonymous  and @v-kelly-msft ,

I would not recommend to create calculated columns via DAX if they are static. There are many reasons - speed downgrade, not optimal compression of data in Vertipaq Engine (xVelocity). You can read about it at a book by Alberto Ferrari and Marco Russo "The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI". Here is free chapter about what I just said - https://www.microsoftpressstore.com/articles/article.aspx?p=2449192.

@Anonymous ,

Under posting PowerQuery code I meant entire code. So to do that right click on your query and press Advanced Editor:

And post a code you received in a window here

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups