Retrieve previous value per account

[nhoff]

For a data set with three colums (Date, Account, Value) I want for each account to retrieve the value for the previous date.

Please see below example data. The last column ("Previous value") is the one I am looking for.

Date Account Value Previous value
15-01-2015 Alpha 79 0
23-01-2015 Beta 81 0
27-01-2015 Gamma 57 0
05-02-2015 Alpha 225 79
06-02-2015 Beta 200 81
06-02-2015 Gamma 94 57
14-02-2015 Alpha 185 225
16-02-2015 Beta 218 200
20-02-2015 Gamma 168 94
03-03-2015 Alpha 193 185
06-03-2015 Beta 72 218
14-03-2015 Gamma 166 168
18-03-2015 Alpha 104 193
21-03-2015 Beta 51 72
07-04-2015 Gamma 119 166
13-03-2015 Alpha 181 104
14-04-2015 Beta 194 51
21-01-2015 Gamma 209 119



I have tried to search for a solution for this, and I am almost there. I am using:

=CALCULATE(MAX(tbl1[Value]);ALL(tbl1);tbl1[Date] < EARLIER(tbl1[Date];1);tbl1[Account]=EARLIER(tbl1[Account];1))

The problem with this calculation is that it only works if Value is always increasing. This is caused by (as I see it) the use of the MAX function. In my case sometimes the value falls and the problem with this is apparent from the above example.

Any input or solution would be appreciated. My preference is to handle it in Power Pivot but an approach involving standard Excel would also be welcome.

Thanks in advance.

 

[nhoff]

Closing this as I received an answer from the powerpivotpro site (Retrieve earlier value for same account - PowerPivotPro)

Solution:

= MINX (
TOPN (
1,
FILTER (
Tbl1,
[Account] = EARLIER ( Tbl1[Account] )
&& [Date] < EARLIER ( tbl1[Date] )
),
[Date]
),
[Value]
)