Forum Discussion
Cummulative sum by week for bar graph
Hi guys,
I need cummulative sum of "Quantity" as per week.
eg week 1
week2=week2+week3......
so far
Problem here is ,source gives the one amount for whole month,which we have broken down in 30 days.(cummulative will result in final result).But not able to break it week wise to get weekly cummulative sum.I have alreday tried below DAX function which does not work.
Cumulative Quantity =
CALCULATE (
SUM ( (quantity]),
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] <= MAX ( 'Date'[Date] )
)
)
| Date | quantity | Year | Month | Year Month |
| Tuesday, May 1, 2018 | 830 | 2018 | 5 | 20185 |
| Friday, June 1, 2018 | 1200 | 2018 | 6 | 20186 |
| Sunday, July 1, 2018 | 25 | 2018 | 7 | 20187 |
| Wednesday, August 1, 2018 | 5 | 2018 | 8 | 20188 |
| Saturday, September 1, 2018 | 3800 | 2018 | 9 | 20189 |
| Monday, October 1, 2018 | 100 | 2018 | 10 | 201810 |
| Thursday, November 1, 2018 | 1400 | 2018 | 11 | 201811 |
| Saturday, December 1, 2018 | 3345 | 2018 | 12 | 201812 |
| Saturday, December 1, 2018 | 55 | 2018 | 12 | 201812 |
structure:
Table 1 joined with DATE table
Any suggestion would be appreciated.
2 Replies
TeigeGaoSolution Sage
Hi Anonymous ,
Could you please share the expected result and the target visual to us? I can't understand your requirement very well. Why will "week2=week2+week3......", besides, your sample data are all the first day of each month rather than a week.
Generally, when we want to get the cummulative sum of data in a week, for example, monday it will display monday, Tuesday displays the sum of Monday and Tuesday, we will create a week column in the calendar table.
Best Regards,
Teige