Forum Discussion

joc's avatar
joc
Helper I
9 years ago
Solved

Cumulative values

Hello everybody,   I need your help for a problem. I would like a CUMULATIVE_VALUE in my table. This value is cumulate in fonction of the WEEK and the MONTH. It's easier to understand with tables ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi joc,

     

    You can use year and month column to create a date column ,then use it as the index to calculate the rolling total.

     

    Sample:

    Date = DATEVALUE([Month]&" "&[Year])

     

     

    Cumulative values:

     

    Rolling Total = CALCULATE(SUM(Sheet4[Value]),FILTER(ALL(Sheet4),[Year]=EARLIER(Sheet4[Year])&&[Week]=EARLIER(Sheet4[Week])&&[Date]<=EARLIER(Sheet4[Date])))

     

     

    Regards,

    XIaoxin Sheng

  • joc's avatar
    joc
    9 years ago

    Anonymous  Perfect ! Thanks !

vanessafvg's avatar
vanessafvg
Community Champion
9 years ago

joc to create a cummulative value you will either need an index in your table in the correct sequence or alternatively have a separate date table that links to your table

  • joc's avatar
    joc
    Helper I
    9 years ago

    Thank you vanessafvg. 

     

    I add a new row "SORT" where JANUARY=1, FEBRUARY=2... but the résult is not good.

     

    CUMULATIVE_VALUE = 
VAR sort = 'table'[SORT]
RETURN
    CALCULATE (
        SUM ( 'table'[VALUE] );
        FILTER (
            'table';
            'table'[SORT] <= SORT
        )
    )

    I don't know how take into account the WEEK !

    Thank you for the help,

    Joc

    • vanessafvg's avatar
      vanessafvg
      Community Champion
      9 years ago

       

      joc that doesn't sound right each row must have a unique index