Forum Discussion
Cumulative sum Problem
My Cumulative Sum measure is not calculating correctly.
I have a "Is In Range" column that show 1 for weekday starting today and 0 for weekend.
I want to make a table out of it with first column as date that contains only weekdays starting from today, Second column as Delayed that has the numbers as it is in the below table and third column as Cumulative Sum where first number in the cumulative sum column is the one which is on Today level on delayed coloumn (in this case 18 may). Example in below case-232 and not 358.
| Date | Is In Range: 1 for weekday | Delayed | Cumulative Sum |
| Sunday, 8 May 2022 | 0 | ||
| Monday, 9 May 2022 | 0 | ||
| Tuesday, 10 May 2022 | 0 | ||
| Wednesday, 11 May 2022 | 0 | 1 | 1 |
| Thursday, 12 May 2022 | 0 | 36 | 37 |
| Friday, 13 May 2022 | 0 | 17 | 54 |
| Saturday, 14 May 2022 | 0 | 54 | |
| Sunday, 15 May 2022 | 0 | 54 | |
| Monday, 16 May 2022 | 0 | 17 | 71 |
| Tuesday, 17 May 2022 | 0 | 55 | 126 |
| Wednesday, 18 May 2022 | 1 | 232 | 358 |
| Thursday, 19 May 2022 | 1 | 44 | 402 |
| Friday, 20 May 2022 | 1 | 110 | 512 |
| Saturday, 21 May 2022 | 0 | 512 | |
| Sunday, 22 May 2022 | 0 | 512 | |
| Monday, 23 May 2022 | 1 | 512 | |
| Tuesday, 24 May 2022 | 1 | 512 | |
| Wednesday, 25 May 2022 | 1 | 512 | |
| Thursday, 26 May 2022 | 1 | 512 | |
| Friday, 27 May 2022 | 1 | 512 | |
| Saturday, 28 May 2022 | 0 | 512 | |
| Sunday, 29 May 2022 | 0 | 512 | |
| Monday, 30 May 2022 | 1 | 512 | |
| Tuesday, 31 May 2022 | 1 | 512
|
4 Replies
learner03 , Are you looking for WTD. Not very clear with you example
WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay]) ))where week rank is column
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatThe information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.Hi,
In calculating the cumulative sum column, what is the relevance of the 1's and 0's in the "Is in range" column?
Hi Ashish_Mathur The cumulative Sum should start from the range where it says 1. This is because 1 starts from Today. So, in this case I need cumulative sum to start from 232 instead of 358.
