Query to return difference between two date/time in same column

[Anonymous]

I am trying to create a new column that will give the time difference between sequental rows. For example the new column would give the difference of 0 seconds between the first two rows. I tried using DAX to create this but kept getting the error of not having enough memory. Im hoping I can do this in the query editor.

[v-easonf-msft]

Hi @Anonymous 

Could you please tell me whether your problem has been solved?

If it is,  please mark the helpful replies or your reply as Answered to close this thread?

Best Regards,
Community Support Team _ Eason

[AiolosZhao]

Hi @Anonymous ,

I'm not very familiar with the M query, so I didn't find a table function to solve that.

But I have an idea to make this, I think it will work, and if anyone can do it in M query I will be glad to see that.

Steps:

1. Copy your table, we call it B table(original one is A table).

2. Add index(from 1) in A table and also add index(from 0) in B table

3. Merge A and B using index, then you will get the next line value.

4. then you can calculate the difference.

Hope I have clarified the method.

Aiolos Zhao

[Anonymous]

Still getting the error of not enough memory

[amitchandak]

Not sure applicable for your case.

I created 150K rows of dates. Taken to Power BI in unsorted order. In edit query/ transform data. I sorted and created an index column. As in my case, I wanted dates to be in sorted order of time; to take diff. No other Primary key option. Now in your case you need to find one. In case you have created date/Updated date, from DB. Basically line number.  You can sort on those

Then I created this column

Previous Row Date = MINX(filter(Sheet1,Sheet1[Index]=(EARLIER(Sheet1[Index])-1)),Sheet1[Date])

This gave the answer quickly.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[amitchandak]

Refer

https://community.powerbi.com/t5/Desktop/Get-previous-row-value-in-new-column/td-p/496182