Can I compare values in the same column in adjacent rows in PowerPivot?

Question

I have a PowerPivot table for which I need to be able to determine how long an item was in an Error state. My data set looks something like this:

What I need to be able to do is to look at the values in the ID and State columns, and see if the value in the previous row is ERROR in the State column, and the same in the ID column. If it is, I then need to calculate the difference between the Changed Date values in those two rows.

So, for example, when I got to row 4, I would see that the value in the State column for Row 3, the previous row, is ERROR, and that the value in the ID column in the previous row is the same as the current row, so I would then calculate the difference between the Changed Date values in Row 3 and Row 4 (I don't care about the values in any of the other columns for this particular requirement).

Is there a way to do this in PowerPivot? I've done a fair amount of Internet searching, and it looks like if it can be done, it would use the EARLIER or EARLIEST DAX functions, but I can't find anything that tells me how, or even if, this can be done.

Thanks.

Answer 1

Chris,

I have had similar requirements many times and after a really long time of trial-and-error, I finally understood how EARLIER works. It can be very powerful, but also very slow so always check for the performance of your calculations.

To answer your question, you will need to create 4 calculated columns:

1) Item Rank - used for ranking the issues with same Item ID

=COUNTROWS(FILTER('ID', EARLIER([Item ID]) = [Item ID] && EARLIER([Date]) >= [Date]))

2) Follows Error - to easily find issue that follows EROR issue

=IF([State] = "EROR",[Item Rank]+1)

3) Time of Following Issue - simple lookup so that you can calculate the different

=IF([Follows Error]>0, 
  LOOKUPVALUE([Date], [User], [User], [Item Rank], [Follows Error]), 
  BLANK()
)

4) Time Diff - calculation of time different for the specific issue

=IF([State]="EROR",
  DAY([Time of Following Issue])-DAY([Date]),
  BLANK()
)

With those calculated columns, you can then easily create a powerpivot table, drag State and Item Id onto the ROWS pane and then simply add Time Diff to Values. You will get an overview of issues that contain string "EROR" issue and the time it took to resolve them.

This is what it looks like in PowerPivot window:

And the resulting Pivot table:

You can download my Excel file here (2013).

As I mentioned, be careful with the performance as the calculated columns with nested EARLIER and IF conditions might be a bit too performance-demanding. If there is a smarter way, I would be very happy to see it, but for now this works for me just fine.

Also, keep in mind that all calculated columns could be nested into 1, but I kept them separated to make it easier to understand the formulas.

Hope this helps :-)