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, 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
)
Thank you very much v-jiascu-msft, this is the solution!
mdjlambeens, You are welcome. I am so glad it helps.