cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## 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.

1 ACCEPTED SOLUTION
Community Support

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Community Support

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

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?

New Member

Thank you so much for the support, it worked.

Super User

why does it need to be a column and not a measure?  do you have a date table?

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Announcements