Cumulative Values

[Anonymous]

Hi,

I have monthly data that needs to also be changed into cumulative data. I have more than 1 column to switch to cumulative. I was able to pull it with 1 of the columns with this calculated column:;

Cumulative Vol = CALCULATE(

    SUM(Sheet1[Vol ]),

    Sheet1[Month]<=EARLIER(Sheet1[Month])

    )

But when I use the same formula with just different column names as such;

Cumulative NNS = CALCULATE(

    sum(Sheet1[NNS]),

    Sheet1[Month]<=EARLIER(Sheet1[Month])

    )

I get this error message;

"A circular dependency was detected: Sheet1[Cumulative Vol], Sheet1[Cumulative NNS], Sheet1[Cumulative Vol]."

Thanks in advance

[TeigeGao · Accepted solution]

Hi @Anonymous ,

We can change the query as the following one:

Cumulative Vol = CALCULATE(
SUM(Sheet1[VOL]),
FILTER(ALL(Sheet1),Sheet1[Month]<=EARLIER(Sheet1[Month])
))

Cumulative NNS = CALCULATE(
sum(Sheet1[NNS]),
FILTER(ALL(Sheet1),Sheet1[Month]<=EARLIER(Sheet1[Month])
))

The result will like below:

Best Regards,

Teige

[parry2k]

@Anonymous here is great article on this, hope it helps.