Forum Discussion
Getting the next date/time from the same column
Hello!
I am working with a SalesForce database and have run into a problem. What I want to acquire is the next Created Date of an opportunity so I can measure the time between different entries and stages.
I want to use the following expression:
Next CreatedDate = CALCULATE(MIN('Opportunity History'[CreatedDate]);
FILTER('Opportunity History';'Opportunity History'[OpportunityId]=EARLIER('Opportunity History'[OpportunityId]));
FILTER('Opportunity History';'Opportunity History'[CreatedDate] > EARLIER('Opportunity History'[CreatedDate]))
)
However, I can not get this to work because PowerBi runs out of memory before It can complete the operation (I have 16g RAM).
If I do the following it does work, but of course then entries on the same date do not get 0 day duration but both take the duration to the next day:
Next CreatedDate = CALCULATE(MIN('Opportunity History'[CreatedDate]);
FILTER('Opportunity History';'Opportunity History'[OpportunityId]=EARLIER('Opportunity History'[OpportunityId]));
FILTER('Opportunity History';'Opportunity History'[CreatedDate].[Date] > EARLIER('Opportunity History'[CreatedDate].[Date]))
)
I have tried:
1. Creating a index column with whole numbers and comparing based on that instead of date/time
2. Creating columns with hour/minute/seconds as whole numbers of created date and adding filters based on those (and using >= .[Date]
However I still run out of memory. I have tried to implement a lookupvalue function but I can't seem to find any functionality with filter expressions so I had no luck there.
I have ran out of ideas here and would appreciate any help.
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
)
14 Replies
Hi mdjlambeens,
Functions like EARLIER will struggle with larger datasets. I think you should perservere with an index column as this will make it easier to find the next record. How did you apply the index? Did you add it using the Query Editor?
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?
