Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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 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
Proud to be a Super User!
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
@joc that doesn't sound right each row must have a unique index
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |