Forum Discussion

mdjlambeens's avatar
mdjlambeens
Frequent Visitor
9 years ago
Solved

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 an...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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
        )

     

     

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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?

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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]
                        )
                    )
                 )
  • mdjlambeens's avatar
    mdjlambeens
    Frequent Visitor
    9 years ago

    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


    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      Hi mdjlambeens,

       

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

       

       

      • mdjlambeens's avatar
        mdjlambeens
        Frequent Visitor
        9 years ago

        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