Forum Discussion

AlbertJan's avatar
AlbertJan
Frequent Visitor
4 years ago
Solved

Cummulative sales figures

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!

 

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

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

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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] )
    )
)
  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      AlbertJan
      Frequent Visitor
      4 years ago

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