cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

 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
1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
4 REPLIES 4
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
Post Partisan

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.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors