Forum Discussion
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 :
I have this table :
And I would like this table with the new column :
Thank a lot for the help !
Joc
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
Anonymous Perfect ! Thanks !
5 Replies
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
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
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
Anonymous Perfect ! Thanks !
