PowerPivot Calculated Field Filtering by value on row

[inincubus]

I am very new to PowerPivot..


I have a table that looks like this:
[TABLE="width: 174"]
<tbody>[TR]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]188[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]190[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]298[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]186[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]147[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]127[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]198[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]132[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]160[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]183[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]117[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]126[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I'd like to add a column with calculated field that shows cumulative sales up to that month which will look like this:

[TABLE="width: 142"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Sales[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]188[/TD]
[TD="align: right"]220[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]378[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]298[/TD]
[TD="align: right"]676[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]186[/TD]
[TD="align: right"]862[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]990[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]1137[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]127[/TD]
[TD="align: right"]1264[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]198[/TD]
[TD="align: right"]1462[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]1594[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]1754[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]183[/TD]
[TD="align: right"]1937[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]2054[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]2180[/TD]
[/TR]
</tbody>[/TABLE]

I used this formula but doesn't seem to work..: =CALCULATE(SUM([SalesTotal]),FILTER(Salestbl,[Month]<[Month]))

Thanks in advance!

 

[ImkeF]

You need to "leave" the context of your current row in order to compare against another month.

Use EARLIER like this: =CALCULATE(SUM([SalesTotal]),FILTER(Salestbl,[Month]<=EARLIER([Month])))

See: The DAX EARLIER Function | Power Pivot | Tiny Lizard

 

[inincubus]

You need to "leave" the context of your current row in order to compare against another month.

Use EARLIER like this: =CALCULATE(SUM([SalesTotal]),FILTER(Salestbl,[Month]<=EARLIER([Month])))

See: The DAX EARLIER Function | Power Pivot | Tiny Lizard

Thank you! I thought PowerPivot is just pivot table with database connections but I couldn't be more wrong!