DAX formula for SUM of previous value in column

[yurykrav]

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):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Obervation[/TD]
[TD]Customer[/TD]
[TD]Location[/TD]
[TD]Product[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]381101[/TD]
[TD]NY[/TD]
[TD]34221[/TD]
[TD]43241[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]381101[/TD]
[TD]NY[/TD]
[TD]14321[/TD]
[TD]12321[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]381101[/TD]
[TD]NY[/TD]
[TD]34223[/TD]
[TD]13413[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]381101[/TD]
[TD]NY[/TD]
[TD]65446[/TD]
[TD]13124[/TD]
[/TR]
</tbody>[/TABLE]

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]

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]

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