Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to calculate “Cumulative”

Hi everyone, 

I've a calculated column named "WeekNum" that returns the number of week of a date and a measure named "Total Sellouts" that calculates total sellouts for each week. 

The formula of the measure is the following:  
 
Total SellOuts =  

var x = CALCULATE(SUM('MyTable'[Qt_1]),FILTER('MyTable', 'MyTable'[Date].[Year] = YEAR(TODAY()))) 

var x = CALCULATE(SUM('MyTable'[Qt_2]),FILTER('MyTable', 'MyTable'[Date].[Year] = YEAR(TODAY()))) 

return x - y 
 
This is my current situation: 
 
WeekNum  (calculated column)                  Total Sellouts (Measure)   

1 

33 

2 

16 

 3 

7 

 4 

4 

 5 

8 

 6 

4 

 7 

16 

 8 

12 

 
 

I need to obtain the "Cumulative" column:
 
Weeks (calculated column)      Total SellOuts (Measure)                   Cumulative (Measure) 

1 

33 

33 

2 

16 

49 

3 

7 

56 

4 

4 

60 

5 

8 

68 

6 

4 

72 

7 

16 

88 

8 

12 

100 

  

Cumulative:

33 = 33

49 = 33 + 16

56 = 33 + 16 + 7

60 = 33 + 16 + 7 + 4

..

 

Thanks  

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi there, I solved my problem.

     

    Cumulativo =
    SUMX (FILTER (ALLSELECTED ( 'MyTable'[WeeksNumber] ), 'MyTable'[WeeksNumber] <= MAX('MyTable'[WeeksNumber])), [SellOuts])
     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi there, I solved my problem.

     

    Cumulativo =
    SUMX (FILTER (ALLSELECTED ( 'MyTable'[WeeksNumber] ), 'MyTable'[WeeksNumber] <= MAX('MyTable'[WeeksNumber])), [SellOuts])
     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello Anonymous ,

     

    Try with the measure,

    unning Total COLUMN =
    CALCULATE (
    SUM ( 'MyTable'[Total Sellouts] ),
    ALL ( 'MyTable' ),
    ( 'MyTable'[Date] ) <= EARLIER ( 'MyTable'[Date] )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous
      I can't use CALCULATE(SUM('MyTable'[Total Sellouts]) because Total Sellouts is already a measure. 

      Total SellOuts =  

      var x = CALCULATE(SUM('MyTable'[Qt_1]),FILTER('MyTable', 'MyTable'[Date].[Year] = YEAR(TODAY()))) 

      var x = CALCULATE(SUM('MyTable'[Qt_2]),FILTER('MyTable', 'MyTable'[Date].[Year] = YEAR(TODAY()))) 

      return x - y 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hello Anonymous ,

         

        Can you able to provide sample data with date column.