Referencing previous row in column: Performance issues

[Anonymous]

Hi, I would like to create a calculated column that references the previous row (in respect to a date/time column). I have a solution, but it requires too much computation to finish (I am running the column on 24 million rows, it has been calculating for 15 hours).

I have included a table below as an example, [Goal Column] is the column I am trying to calculate. As you can see, it is referencing the [hook_load] for the prior [Regular Date Time.1] (which also happens ot be the prior row in the sorting of this example table). 

I've also included a screenshot of my currently existing code (which takes too long). It's still calculating so I can't include a text version. 

Thanks in advance, I appreicate your support! 

time_traces_10s_view
Well	Regular Date Time.1	hook_load	Goal Column (previous [hook_load]_
justin	6/1/2021 5:00	10	-
justin	6/1/2021 5:10	5	10
justin	6/1/2021 5:20	4	5
justin	6/1/2021 5:30	5	4
justin	6/1/2021 5:40	7	5
justin	6/1/2021 5:50	5	7
justin	6/1/2021 6:00	6	5
justin	6/1/2021 6:10	5	6
brett	6/4/2021 2:10	4	-
brett	6/4/2021 2:20	5	4
brett	6/4/2021 2:30	8	5
brett	6/4/2021 2:40	4	8
brett	6/4/2021 2:50	5	4
brett	6/4/2021 3:00	7	5
brett	6/4/2021 3:10	5	7

[selimovd · Accepted solution]

Hey @Anonymous ,

what exactly is your data source? You could check if the approach in Power Query is faster or more efficient than in DAX. Check the approach in Power Query:

Value from previous row – Power Query, M language – Trainings, consultancy (exceltown.com)

Or

Refer to Previous Row and Next Row in Power Query - Goodly

Otherwise is it possible to add an index column in Power Query? Then you can just filter the rows to [index]-1 to get the previous row. That should be faster from my feeling:

Find a Value in the Previous Row | Burningsuit

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic