cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ley
Helper I
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 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,

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @Ley

 

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

Annotation 2020-01-31 163943.png

 

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:

 

Annotation 2020-01-31 164118.png

 

For the related .pbix file,pls click here.

 

Best Regards,
Kelly

View solution in original post

1 REPLY 1
v-kelly-msft
Community Support
Community Support

Hi @Ley

 

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

Annotation 2020-01-31 163943.png

 

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:

 

Annotation 2020-01-31 164118.png

 

For the related .pbix file,pls click here.

 

Best Regards,
Kelly

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors