cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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 - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors