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 Phil_Seamark,
Yes I applied the index in the query editor through the "Index Column" function after sorting "Created Date" ascendingly. I then changed the data type to whole number (from decimal) since I thought this would make the expression easier on PowerBi.
I then used the following:
Next CreatedDate = CALCULATE(MIN('Opportunity History'[CreatedDate]);
FILTER('Opportunity History';'Opportunity History'[OpportunityId]=EARLIER('Opportunity History'[OpportunityId]));
FILTER('Opportunity History';'Opportunity History'[Index] > EARLIER('Opportunity History'[Index]))
)
However, this also causes a memory timeout.
I thought of something that could potentially work, adding an index that resets for every unique Opportunity, meaning PBI would have to use much smaller numbers. However, I am unable to figure out how to implement this.
The main thing is to get the index in without the index causing the issue and leave it as a whole number.
Any chance you can post a small sample of your data with the index column so I can have a crack ag a measure that isn't so heavy on EARLIER?
In the meantime.
Can you try adding this calculated column?
Next Date = CALCULATE( MIN('Opportunity History'[CreatedDate]), FILTER( 'Opportunity History', 'Opportunity History'[Index]=EARLIER('Opportunity History'[Index]) +1 && 'Opportunity History'[OpportunityID] = EARLIER('Opportunity History'[OpportunityID] ) ) )Sure, no problem! I've also added my current stage duration calc to better illustrate where it (expectantly) goes wrong.
