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

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

Reply
Ram_DCT
Helper II
Helper II

Split Cumulative Sum

Hi Team,

 

I am looking to split my cumulitive sum and get day wise hours spent on a work item id.

Please ntoe that, for each wokr item - date column would be be continuos and we need to split cumulative by each day

 

Here is the raw data;

Work Item IdDateAssigned to_UsersStateCompleted Work
81177/22/2021User1In Progress4
81177/23/2021User1In Progress5
81177/24/2021User1In Progress6
81177/25/2021User1In Progress7
81177/26/2021User1In Progress8
81177/27/2021User1In Progress8
81177/28/2021User1Code Review10
81177/29/2021User1Code Review11
81177/30/2021User2Code Review11
81177/31/2021User2Code Review12
81178/1/2021User2Code Review13
81178/2/2021User3Testing13
81178/3/2021User3Testing13
81187/22/2021User1In Progress1
81187/23/2021User1In Progress3
81187/24/2021User1In Progress8
81187/25/2021User1In Progress8
81187/26/2021User1In Progress11
81187/27/2021User1In Progress11
81187/28/2021User1Code Review14
81187/29/2021User1Code Review14
81187/30/2021User2Code Review15
81187/31/2021User2Code Review15
81188/1/2021User2Code Review15
81188/2/2021User3Testing18
81188/3/2021User3Testing19

 

Expected Result with two additional calculated cloumns:

Work Item IdDateAssigned to_UsersStateCompleted WorkCalculated1Final Result
81177/22/2021User1In Progress404
81177/23/2021User1In Progress541
81177/24/2021User1In Progress651
81177/25/2021User1In Progress761
81177/26/2021User1In Progress871
81177/27/2021User1In Progress880
81177/28/2021User1Code Review1082
81177/29/2021User1Code Review11101
81177/30/2021User2Code Review11110
81177/31/2021User2Code Review12111
81178/1/2021User2Code Review13121
81178/2/2021User3Testing13130
81178/3/2021User3Testing13130
81187/22/2021User1In Progress101
81187/23/2021User1In Progress312
81187/24/2021User1In Progress835
81187/25/2021User1In Progress880
81187/26/2021User1In Progress1183
81187/27/2021User1In Progress11110
81187/28/2021User1Code Review14113
81187/29/2021User1Code Review14140
81187/30/2021User2Code Review15141
81187/31/2021User2Code Review15150
81188/1/2021User2Code Review15150
81188/2/2021User3Testing18153
81188/3/2021User3Testing19181

 

Testing:

1. Cumulative sum of the completed hours for work item id 8117 is 13 [As per the COMLETED WORK column] - same we will get from FINAL RESULT column when we sum the values

2. Cumulative sum of the completed hours for work item id 8118 is 19 [As per the COMLETED WORK column] - same we will get from FINAL RESULT column when we sum the values

 

Please help as soon as possble.

 

-Ram

4 REPLIES 4
v-xiaotang
Community Support
Community Support

Hi @Ram_DCT 

you can try this,

Calculated1 = CALCULATE(MAX('Table'[Completed Work]),FILTER(ALLEXCEPT('Table','Table'[Work Item Id]),'Table'[Date]<EARLIER('Table'[Date],1)))
Final Result = 'Table'[Completed Work]-'Table'[Calculated1]

result

vxiaotang_0-1630401525501.png

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

 

VijayP
Super User
Super User

@Ram_DCT 
Try using this pbix file




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Ram_DCT
Helper II
Helper II

Can someone please help me here. Even if it is work of Power Query.

 

daxer-almighty
Solution Sage
Solution Sage

This is a job for Power Query, not DAX.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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