Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cumulative Values

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
Need Help
Tutorial Requests
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    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

2 Replies

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    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