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

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?

  • mdjlambeens's avatar
    mdjlambeens
    Frequent Visitor
    9 years ago

    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.

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