Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
learner03
Post Partisan
Post Partisan

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.

 

DateIs In Range: 1 for weekdayDelayedCumulative Sum
Sunday, 8 May 20220  
Monday, 9 May 20220  
Tuesday, 10 May 20220  
Wednesday, 11 May 2022011
Thursday, 12 May 202203637
Friday, 13 May 202201754
Saturday, 14 May 20220 54
Sunday, 15 May 20220 54
Monday, 16 May 202201771
Tuesday, 17 May 2022055126
Wednesday, 18 May 20221232358
Thursday, 19 May 2022144402
Friday, 20 May 20221110512
Saturday, 21 May 20220 512
Sunday, 22 May 20220 512
Monday, 23 May 20221 512
Tuesday, 24 May 20221 512
Wednesday, 25 May 20221 512
Thursday, 26 May 20221 512
Friday, 27 May 20221 512
Saturday, 28 May 20220 512
Sunday, 29 May 20220 512
Monday, 30 May 20221 512
Tuesday, 31 May 20221 

512

 

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

In calculating the cumulative sum column, what is the relevance of the 1's and 0's in the "Is in range" column?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.