Power Pivot previous value

Question

I have the following table & data that can be seen in Excel PowerPivot

item, timeframe, total
1, 1, 15
1, 2, 20
1, 3, 15
2, 1, 10
2, 2, 11
2, 3, 10

While I can easily get the last timeframe, I need to get the previous timeframe's total like:

item, timeframe, total, last_timeframe, last_timeframe_total
1, 1, 15, 0, 0
1, 2, 20, 1, 15
1, 3, 15, 2, 20
2, 1, 10, 0, 0
2, 2, 11, 1, 10
2, 3, 10, 2, 11

I've tried a calculate formula, but that didn't seem to work and only returns blanks. =CALCULATE(SUM(MyTable[total]), MyTable[timeframe] = EARLIER(MyTable[timeframe]) - 1)

I did ... I posted what I tried, which is in the last line. I've tried lots of variations on that, but they all returned blanks or errors. — Randy Walker, Nov 23 '15 at 22:59

score 2 · Accepted Answer · answered Nov 23 '15 at 23:05

EARLIER() doesn't understand any sort of ordering of rows.

EARLIER() refers to nested row contexts.

What you actually want is LOOKUPVALUE() here, which matches the values in specified fields with search criteria you provide, and returns the value that exists for the row which matches those criteria.

Based on your sample it looks like [Timeframe] is a one-incremented index for each item. If this assumption is not true, LOOKUPVALUE() is probably not the function you want.

last_timeframe_total =
LOOKUPVALUE(
    MyTable[total]
    ,MyTable[item]    // This is the field we're searching
    ,MyTable[item]    // This is the value to search for in the field
                      // identified in argument 2 - this evaluates
                      // in the current row context of the table
                      // where we are defining last_timeframe_total
                      // as a calculated column.
    ,MyTable[timeframe] // The second field we are searching.
    ,MyTable[timeframe] - 1 // Similar to argument 3.
)

This will give you the value for the prior timeframe for the current item.

Ninja edit: Forgot to mention that Power Pivot isn't really the layer to be doing this sort of work in. Lookups and this sort of data shaping are better done in your ETL from transactional sources. If this is not possible, then it's better done in the query to populate Power Pivot than in Power Pivot. Power Query is a good tool to use for this sort of transformation that easily fits into the Microsoft ecosystem, being another Microsoft add-in for Excel.

Power Pivot is an analytical database optimized for aggregations. In general, if you ever find yourself thinking "for every row," it's a sign that what you're trying to accomplish is probably better suited for a different layer of the BI solution.

Awesome. Thank you very much!! Do you have any good references for Calculate and Earlier? All of the MSDN articles and blog postings didn't do a very good explanation of the two functions, nor good examples for beginners. — Randy Walker, Nov 23 '15 at 23:12
PowerPivotPro.com and SQLBI.com. Read everything they post. And then start reading through DAXPatterns.com — greggyb, Nov 24 '15 at 00:15

Power Pivot previous value

1 Answers1