cancel
Showing results for
Did you mean:  Helper I

Incremental sum taking into account previous rows

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,

1 ACCEPTED SOLUTION  Community Support

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: Best Regards,
Kelly  Community Support

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: Best Regards,
Kelly  