Forum Discussion
Getting the next date/time from the same column
Hello, mdjlambeens,
It seems that you really have a big table. Due to FILTER generate a n*n calculation, we should avoid it. Here may be the solution. It worked. You can have a try.
1. Open query editor. Sorted by "OpportunityId";
2. Open ADVANCED EDITOR (Sign 2), then add ", {"CreatedDate", Order.Ascending}" (sign 3).
3. Click DONE, you will see two sorted arrows (in yellow square).This is what we want.
4. Add index.
5. Add a calculated column with this formula.Next CreatedDatevar =
VAR CurrentIndex = 'Opportunity History'[IndexNew]
RETURN
CALCULATE (
MIN ( 'Opportunity History'[CreatedDate] ),
ALLEXCEPT ( 'Opportunity History', 'Opportunity History'[OpportunityId] ),
'Opportunity History'[Indexnew]
= currentindex + 1
)
Hello v-jiascu-msft,
Thank you for your help!
I saw and tried this solution when it was posted by @v-qiuyu-MSFT earlier. I was about to reply but the submission was removed.
I tried this solution in its current form and by replacing createdDate with index. Unfortunately, although it takes markedly longer, I still run out of memory..
Hello, mdjlambeens,
It seems that you really have a big table. Due to FILTER generate a n*n calculation, we should avoid it. Here may be the solution. It worked. You can have a try.
1. Open query editor. Sorted by "OpportunityId";
2. Open ADVANCED EDITOR (Sign 2), then add ", {"CreatedDate", Order.Ascending}" (sign 3).
3. Click DONE, you will see two sorted arrows (in yellow square).This is what we want.
4. Add index.
5. Add a calculated column with this formula.
Next CreatedDatevar =
VAR CurrentIndex = 'Opportunity History'[IndexNew]
RETURN
CALCULATE (
MIN ( 'Opportunity History'[CreatedDate] ),
ALLEXCEPT ( 'Opportunity History', 'Opportunity History'[OpportunityId] ),
'Opportunity History'[Indexnew]
= currentindex + 1
)