Forum Discussion
Calculated Column - Cumulative sum
I've searched the forum, but the main advice is to create a measure.
I need to create a calculated column, which will sum the cumulative value during the year, for each service in each company.
| Date | Value | Company | Service | Cumulative value |
| Jan-21 | 12413 | a | x | 12413 |
| Feb-21 | 235 | a | x | 12648 |
| Mar-21 | 235235 | a | x | 247883 |
| Jan-21 | 13536 | b | x | 13536 |
| Feb-21 | 4575 | b | x | 18111 |
| Mar-21 | 34533 | b | x | 52644 |
I tried to make it this way:
CALCULATE(SUMX(Sheet1,Sheet1[Value]),FILTER(Sheet1,Sheet1[Date]<=MAX(Sheet1[Date])
Unfortunately this is not the good direction.
Hi pietra ,
Please try this code to create a calcualted column.
Cumulative value = CALCULATE ( SUM ( 'Sheet1'[Value] ), FILTER ( ALLEXCEPT ( 'Sheet1', 'Sheet1'[Company] ), 'Sheet1'[Date] <= EARLIER ( 'Sheet1'[Date] ) ) )My Sample:
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi pietra ,
Please try this code to create a calcualted column.
Cumulative value = CALCULATE ( SUM ( 'Sheet1'[Value] ), FILTER ( ALLEXCEPT ( 'Sheet1', 'Sheet1'[Company] ), 'Sheet1'[Date] <= EARLIER ( 'Sheet1'[Date] ) ) )My Sample:
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for the support, it worked.
I tried using your code in a similar problem I have.. However, it doesn't accept the last part:
EARLIER ( 'Sheet1'[Date] )
it doesn't accept the column name it gives me an error and says "can't find name" even though I just refereenced the same column in the first part:
'Sheet1'[Date] <= EARLIER ( 'Sheet1'[Date] )
any help on how to resolve it?
why does it need to be a column and not a measure? do you have a date table?
