Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
)
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?
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.
No problem! Does this work for you?
OpportunityId,Index,Opportunity_Type__c,StageName,CreatedDate,Stage Duration (days)
0062000000Z6Z6yAAF,390,Secondment,Identified,2014-07-10 10:20:31,62
0062000000Z6Z6yAAF,1907,Secondment,Closed Lost,2014-09-10 13:10:49,0
0062000000Z6Z6yAAF,1908,Secondment,Closed Lost,2014-09-10 13:10:50,0
0062000000bnYHsAAM,5554,Secondment,Proposal,2015-02-02 13:46:29,18
0062000000bnYHsAAM,5556,Secondment,Proposal,2015-02-02 13:48:03,18
0062000000bnYHsAAM,6147,Secondment,Proposal,2015-02-20 09:57:49,26
0062000000bnYHsAAM,7023,Secondment,Proposal,2015-03-18 11:45:58,14
0062000000bnYHsAAM,7496,Secondment,Proposal,2015-04-01 09:01:42,19
0062000000bnYHsAAM,7950,Secondment,Closed Lost,2015-04-20 13:21:01,0
0062000000fOwycAAC,12180,Time & Material,Identified,2015-09-16 12:07:32,63
0062000000fR7eHAAS,12867,Time & Material,Identified,2015-10-12 07:15:32,4
0062000000fR7eHAAS,13094,Time & Material,Closed Stopped,2015-10-16 09:26:18,0
0062000000fOwycAAC,14108,Time & Material,Closed Stopped,2015-11-18 12:01:54,0
0062000000hVRxmAAG,16663,Fixed Price,Identified,2016-01-27 17:34:51,157
0062000000hVRxmAAG,16664,Fixed Price,Identified,2016-01-27 17:36:18,157
0062000000hVRxmAAG,22302,Fixed Price,Closed Stopped,2016-07-02 18:42:09,0
0062000000kr2GPAAY,24898,Fixed Price,Identified,2016-10-03 09:44:24,30
0062000000kr2GPAAY,25840,Fixed Price,Offering,2016-11-02 09:06:19,9
0062000000kr2GPAAY,26180,Fixed Price,Offering,2016-11-11 14:32:45,31
0062000000kr2GPAAY,27194,Fixed Price,Offering,2016-12-12 11:57:25,45
0062000000kr2GPAAY,28857,Fixed Price,Offering,2017-01-26 08:57:35,19
0062000000kr2GPAAY,28858,Fixed Price,Offering,2017-01-26 08:57:48,19
0062000000kr2GPAAY,29543,Fixed Price,Closed Stopped,2017-02-14 07:55:20,0
Any luck yet? I'm stilll stuck.
Hi, @mdjlambeens
Try this formula in the table as a calculated column. It could help.
Next CreatedDate =
CALCULATE (
MIN ( 'Opportunity History'[CreatedDate] ),
ALLEXCEPT ( 'Opportunity History', 'Opportunity History'[OpportunityId] ),
FILTER (
ALL ( 'Opportunity History'[CreatedDate] ),
'Opportunity History'[CreatedDate]
> EARLIER ( 'Opportunity History'[CreatedDate] )
)
)
I know this is an old post but that really helped me, cheers!
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
)
@mdjlambeens, You are welcome. I am so glad it helps.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |