Forum Discussion

Ley's avatar
Ley
Icon for Helper I rankHelper I
6 years ago
Solved

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 CompanyInitialGroupFinalGroupTimeTime2
A3BlackG12G60,00KO
A3WhiteG6G60,01KO
A3WhiteG6G60,01KO
A3WhiteG6G29,049,06
A3WhiteG2G2206,07KO
A3WhiteG2G29,15KO
A5BlackG12G50,00KO
A5WhiteG5G50,64KO
A5WhiteG5G40,010,65
A5WhiteG4G40,03KO
A5WhiteG4G30,100,13
A5WhiteG3G37,53KO

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

Thanks,

  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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

1 Reply

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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