Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mdjlambeens
Frequent Visitor

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.Table

 

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.

1 ACCEPTED 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
    )

Getting the next datetime from the same column02.jpg

 

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

14 REPLIES 14
Phil_Seamark
Employee
Employee

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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]
                        )
                    )
                 )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Sure, no problem! I've also added my current stage duration calc to better illustrate where it (expectantly) goes wrong.

http://puu.sh/vyMgc/c7d8e6c119.jpg


Hi @mdjlambeens,

 

Any chance you can post the data as text?  I'm too lazy to type it in 🙂

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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] )

    )

)

Getting the next datetime from the same column.jpg

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
    )

Getting the next datetime from the same column02.jpg

 

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you very much @v-jiascu-msft, this is the solution!

@mdjlambeens, You are welcome. I am so glad it helps.

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.