locked
How can I calculate a value based on the value of a cell in the current and the next/previous row? RRS feed

  • Question

  • I want to find out the time spent by a customer on a step, as well as if that was the last step he took. Assume table has 3 columns: step, customer, and time.

    In excel I would reference the column and row number, such as: TimeOnStep=if(b2=b3,b3-b2,0)

    and LastStep=if(b2=b3,"No","Yes")

    How do I do that in PowerPivot? is it even possible?

    here's an example:

    step       customer     time          Time on step       last step

    1              a              10:01            2                        no

    2              a               10:03           0                       yes

    1              b               12:23            4                         no

    2              b                12:27            1                        no

    3              b                12:28            0                         yes

    Friday, September 9, 2011 5:54 AM

Answers

  • Hi -ET,

    To this requirement of yours, I think you can make use of DAX to achieve this. In your PowerPivot table, please create two different columns: Time on step/last step.

    To the first column, please type in the following expression:

    =if([customer]=[time],[customer]-[time],0)

    To the second column, please type in the following expression:

    =if([customer]=[time],"Yes","No")

    For more information about PowerPivot function please see: http://powerpivot-info.com/post/52-list-of-powerpivot-dax-functions-with-description

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Edited by Challen Fu Monday, September 12, 2011 8:06 AM
    • Marked as answer by Challen Fu Sunday, September 18, 2011 6:10 AM
    Monday, September 12, 2011 7:57 AM
  • I answered last week but it seems my message has been lost.

    I misread your first email - please try these two calculated columns.

    nextTime

    =CALCULATE(MIN(Data[time]),ALL(Data),Data[step]>EARLIER(Data[step]),Data[customer] = EARLIER(Data[customer]))

     

    Time on Step

    =IF(ISBLANK(Data[nextTime]),BLANK(),Data[nextTime]-Data[time])

     

     


    Marco Russo http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo
    • Marked as answer by Challen Fu Sunday, September 18, 2011 6:10 AM
    Monday, September 12, 2011 8:10 AM

All replies

  • Assuming the table is called Data, this is the formula:

    =CALCULATE( SUM(Data[time on step]), FILTER( Data, Data[step] <= EARLIER( Data[step] ) && Data[customer] = EARLIER( Data[customer] ) ) )

     

    There is no need to use the last step colum in the formula.


    Marco Russo http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo
    Friday, September 9, 2011 4:17 PM
  • This doesn't work...

    1. I need the Last Step indicator for reporting - need to identify what was the last step taken by each user.

    2. IF I understand the "Earlier" function, it always looks up, never down. I need "timestamp on step2"-"timestamp on step1"="time spent on step 1"

     

    Here's what it actually looks like. I'm not even sure what it is calculating it looks completely random.

     

    Step CustomerID Date Time TimeOnStep PP Calculated TimeOnStep Actual LastStep
    1 Customer1 9/6/2011 16:24:00
     16:24 0:02 No
    2 Customer1 9/6/2011 16:26:00  8:51 0:00 No
    3 Customer1 9/6/2011 16:26:00  1:17 0:01 No
    4 Customer1 9/6/2011 16:27:00
     10:13 0:00 No
    5 Customer1 9/6/2011 16:27:00  19:09 0:01 No
    4 Customer1 9/6/2011 16:28:00          
    10:13 0:00 No
    5 Customer1 9/6/2011 16:28:00 19:09 0:01 No
    6 Customer1 9/6/2011 16:29:00 4:09 0:00 Yes
    1 Customer2 9/6/2011 17:32:00 11:05 0:00 No
    2 Customer2 9/6/2011 17:32:00 4:37 0:00 No
    3 Customer2 9/6/2011 17:32:00 22:09 0:00 No
    4 Customer2 9/6/2011 17:32:00 15:42 0:00 No
    5 Customer2 9/6/2011 17:32:00 9:15 0:01 No
    6 Customer2 9/6/2011 17:33:00 2:48 0:00 Yes

     

    BTW, I just recreated this PowerPivot, and now it appears to ignore the group/sort order specified in the SQL code. Not sure there is any way this can work...

    Friday, September 9, 2011 7:46 PM
  • Hi -ET,

    To this requirement of yours, I think you can make use of DAX to achieve this. In your PowerPivot table, please create two different columns: Time on step/last step.

    To the first column, please type in the following expression:

    =if([customer]=[time],[customer]-[time],0)

    To the second column, please type in the following expression:

    =if([customer]=[time],"Yes","No")

    For more information about PowerPivot function please see: http://powerpivot-info.com/post/52-list-of-powerpivot-dax-functions-with-description

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Edited by Challen Fu Monday, September 12, 2011 8:06 AM
    • Marked as answer by Challen Fu Sunday, September 18, 2011 6:10 AM
    Monday, September 12, 2011 7:57 AM
  • I answered last week but it seems my message has been lost.

    I misread your first email - please try these two calculated columns.

    nextTime

    =CALCULATE(MIN(Data[time]),ALL(Data),Data[step]>EARLIER(Data[step]),Data[customer] = EARLIER(Data[customer]))

     

    Time on Step

    =IF(ISBLANK(Data[nextTime]),BLANK(),Data[nextTime]-Data[time])

     

     


    Marco Russo http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo
    • Marked as answer by Challen Fu Sunday, September 18, 2011 6:10 AM
    Monday, September 12, 2011 8:10 AM