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) Ini...
  • 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

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