Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
|
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
Hi,
You may download my PBI file from here.
Hope this helps.
@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 format
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
User | Count |
---|---|
123 | |
96 | |
89 | |
73 | |
63 |
User | Count |
---|---|
139 | |
115 | |
114 | |
98 | |
98 |