Cumulative values

[joc]

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 :

I have this table :

And I would like this table with the new column :

Thank a lot for the help !

Joc

[v-shex-msft · Accepted solution]

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

[v-shex-msft · Accepted solution]

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 · Accepted solution]

@v-shex-msft  Perfect ! Thanks !

[vanessafvg]

@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]

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]

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