# DAX formula for SUM of previous value in column



## yurykrav (Mar 29, 2015)

Hi!
I have a very simple (and in the same time very complicated for me) task.
I have this initial data (about 100 000 rows):


ObervationCustomerLocationProductSales5381101NY34221432415381101NY14321123216381101NY34223134136381101NY6544613124

<tbody>

</tbody>
Very simple - observations (it can be some periods, but no link to calendar dates), Customers, Locations, Products and Sales amount

All I want is to see in calculated field sum of Sales for previous observation - like this:







So I need to create somehow a reference to Observation value in query context and calculate Sum of Sales 
But I can only write a formula for Calculated Field using EARLIER function:

"=calculate(sum([Sales]);filter('Table';[Observation]+1=earlier([Observation])&&[Customer]=earlier([Customer])&&[Location]=earlier([Location])&&[Product]=earlier([Product])))"

It works but it works row by row and if I miss some Customer in Observation 5, I get in total less Sales in my field

Im sure that there is some very simple decision for this case

Thank!


----------



## scottsen (Mar 29, 2015)

Total Sales := SUM(MyTable[Sales])
Prev Sales := CALCULATE([Total Sales], FILTER(ALL(MyTable[Observation]), MyTable[Observation] = MAX(MyTable[Observation]) - 1))

May need something special for the grand total, but this should get you close.


----------



## yurykrav (Mar 30, 2015)

scottsen said:


> Total Sales := SUM(MyTable[Sales])
> Prev Sales := CALCULATE([Total Sales], FILTER(ALL(MyTable[Observation]), MyTable[Observation] = MAX(MyTable[Observation]) - 1))
> 
> May need something special for the grand total, but this should get you close.



Amazing! Its very simple, thanks


----------

