Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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
Basically we get data for first day of the month which is eg.14000000,which we break into month days to display the line graph.
Now problem here is we have to break the amount week wise to display it as cummulative sum.@cha
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |