Need help with cumulative measure

[zenisekd]

Hi,
I have two tables in relationship - Issues and date table. 

Issues table contains:
Issue_ID, Epic ID, First_Sprint_date and Time_spent, component. 

Date table is in relation to issues over first_spritnt_date. 

My aim is to create visual t2 (see bellow). In order to do that, I need to calculate a special running total (2), which I will explain in the t1 and t2. 

t1

EPIC_ID	Issue_ID	First_day_of_sprint	Time spent	Running Total 1
x	x1	2024-03-01	1	0
x	x2	2024-03-15	2	0
x	x3	2024-03-29	1	4
y	y1	2024-03-01	6	0
y	y1	2024-03-15	8	14
z	z1	2024-03-29	3	3

t2

Date	Running total 2
2024-03-01	0
2024-03-15	14 (14)
2024-03-29	7  (4+3)

I also need to be able to slice the t2 by date and epic_ID and component. 

Could somebody please advise me on how to get the Running total 2, so I can create t2? thank you.

[Anonymous]

Hi @zenisekd,

Thanks @sanalytics  and @rohit1991 for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @zenisekd,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[Anonymous]

Hi @zenisekd,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[sanalytics]

Hello @zenisekd 
One Calculated column is enough.. Below is the code

RT1Col = 
VAR _Rannk =
RANK(
    SUMMARIZE( ALLSELECTED('Table'),'Table'[EPIC_ID],'Table'[First_day_of_sprint],'Table'[Issue_ID],'Table'[Time spent] ),
ORDERBY( 'Table'[First_day_of_sprint],ASC),,PARTITIONBY( 'Table'[EPIC_ID] ) )
VAR _TotalCount = 
CALCULATE(
    COUNTROWS( 'Table'),
ALLEXCEPT( 'Table','Table'[EPIC_ID] )
)
VAR _RT = 
CALCULATE(
    SUM( 'Table'[Time spent] ),
WINDOW( 
    1,ABS,
    0,REL,
SUMMARIZE( ALLSELECTED('Table'),'Table'[EPIC_ID],'Table'[First_day_of_sprint],'Table'[Issue_ID],'Table'[Time spent] ),
ORDERBY( 'Table'[First_day_of_sprint],ASC), PARTITIONBY( 'Table'[EPIC_ID]))
)
VAR _Result = 
IF(
 _TotalCount = _Rannk,
 _RT,0
)
RETURN
_Result

Please adjust this code based on your model.

screenshot below

Attached the pbix file for your refernce.

Hope it helps

Regards

sanalytics

[rohit1991]

Hi @zenisekd,
To calculate the special running total you need (Running Total 2), you'll first need to create a logic that identifies which issues should be included based on their position within an EPIC.

From your example, it looks like you want to exclude the first two issues (by date) for each EPIC and only begin summing the Time Spent values starting from the third issue onward. To do this, you can create a calculated column in the Issues table that counts how many earlier issues exist for each EPIC based on the First_Sprint_Date, and flag only those that are third or later. Once identified, another column can assign Time Spent to these flagged issues, treating others as zero. 

After that, you can create a measure that sums this filtered time data across the date dimension cumulatively, by using a DAX measure that calculates the running total over time. This measure (Running Total 2) can then be used in a visual, and it will be fully sliceable by Date, EPIC_ID, and Component thanks to your existing data model. This setup ensures your t2 visual behaves as expected, reflecting only the desired subset of effort accumulation over time.

Step 1: Calculated Column – Flag issues starting from 3rd occurrence per EPIC

IsCountedInRT1 =
VAR CurrentEpic = Issues[EPIC_ID]
VAR CurrentDate = Issues[First_Sprint_date]
VAR RankInEpic =
    CALCULATE(
        COUNTROWS(Issues),
        FILTER(
            Issues,
            Issues[EPIC_ID] = CurrentEpic &&
            Issues[First_Sprint_date] < CurrentDate
        )
    )
RETURN IF(RankInEpic >= 2, 1, 0)

 Step 2: Calculated Column – Running Total 1 (per Issue)

RunningTotal1 =
IF(Issues[IsCountedInRT1] = 1, Issues[Time_spent], 0)

Step 3: Measure – Running Total 2 (Cumulative across Dates)

RunningTotal2 = 
CALCULATE(
    SUM(Issues[RunningTotal1]),
    FILTER(
        ALL('Date'),
        'Date'[Date] <= MAX('Date'[Date])
    )
)