Incremental sum taking into account previous rows

[Ley]

Hi
I need to obtain two calculate columns: 'Time2' and 'Cases'.

In Time2 column we have to calculate the sum of “Time” values, if the following conditions meet:  (1)  Company is “White” and (2) InitialGroup is diferent from FinalGroup. The sum should be conducted until the row in which the second condition meet. Otherwise, “KO” should appear.

Rows are grouped by ID.

An example:

ID	Company	InitialGroup	FinalGroup	Time	Time2
A3	Black	G12	G6	0,00	KO
A3	White	G6	G6	0,01	KO
A3	White	G6	G6	0,01	KO
A3	White	G6	G2	9,04	9,06
A3	White	G2	G2	206,07	KO
A3	White	G2	G2	9,15	KO
A5	Black	G12	G5	0,00	KO
A5	White	G5	G5	0,64	KO
A5	White	G5	G4	0,01	0,65
A5	White	G4	G4	0,03	KO
A5	White	G4	G3	0,10	0,13
A5	White	G3	G3	7,53	KO

I have tried using EARLIER functions, but I haven´t obtain good results.

Thanks,

[v-kelly-msft · Accepted solution]

Hi @Ley ，

Go to "edit queries">"Add column">"Index Column":

Then create a calculated column as below:

Times2 = 
IF (
    [Company] <> "White"
        || [InitialGroup] = [FinalGroup],
    "KO",
    VAR i = [Index]
    VAR d = [ID]
    VAR l =
        CALCULATE (
            MAX ( 'Table'[Index] ),
            FILTER (
                'Table',
                'Table'[ID] = d
                    && [Company] = "White"
                    && [InitialGroup] <> [FinalGroup]
                    && 'Table'[Index] < i
            )
        )
    RETURN
        CALCULATE (
            SUM ( 'Table'[Time] ),
            FILTER (
                'Table',
                'Table'[ID] = EARLIER ( 'Table'[ID] )
                    && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
                    && 'Table'[Index]
                        > IF (
                            l = BLANK (),
                            CALCULATE ( MIN ( 'Table'[Index] ), FILTER ( 'Table', 'Table'[ID] = d )), l 
                        )
            )
        ) & ""
)

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly