Cummulative sales figures

[AlbertJan]

Hello,

I am building a sales dashboard using a table SalesInfoPivot with summarized salesfigures per week. I want to add an extra column with cummulative salesfigures for each week. Part of the table looks like this:

Column CummulativeCount should show the sum of [CountThisYearPerWeek]-values for weeks from 1 until [Weeknumber]. In the example I would expect the values 110 - 257 - 434 - 599 - 757 etc.

I am using the following code:

CummulativeCount = CALCULATE(

SUM( SalesInfoPivot[CountThisYearPerWeek] ),

FILTER(SalesInfoPivot,

SalesInfoPivot[WeekNumber] <= [WeekNumber]))

Apparently the FILTER does not accept or recognize [WeekNumber] and does not filter the data at all.

Any suggustion how to resolve this issue? Thanks in advance!

[AlexisOlson · Accepted solution]

Try adding EARLIER in so that it knows you're referring to the earlier (original) row context rather than the row context from FILTER.

CummulativeCount =
CALCULATE (
    SUM ( SalesInfoPivot[CountThisYearPerWeek] ),
    FILTER (
        SalesInfoPivot,
        SalesInfoPivot[WeekNumber] <= EARLIER ( SalesInfoPivot[WeekNumber] )
    )
)

[AlbertJan]

Thank you, Alexis. You made my day!

[amitchandak]

@AlbertJan , Try

CummulativeCount = CALCULATE(
SUM( SalesInfoPivot[CountThisYearPerWeek] ),
FILTER(allselected(SalesInfoPivot),
SalesInfoPivot[WeekNumber] <= [WeekNumber]))

or

CummulativeCount = CALCULATE(
SUM( SalesInfoPivot[CountThisYearPerWeek] ),
FILTER(all(SalesInfoPivot),
SalesInfoPivot[WeekNumber] <= [WeekNumber]))

[AlbertJan]

Thanks for yor reply. Unfortunately this does not solve the problems. Rows are not filtered.