Reply to earlier post on calculated column from two col. to add previous rows only if 1st col. is 0

[AdityaG]

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculated-column-from-two-columns-to-add-pre...

Reference to the above query. Somehow my replies are getting automatically deleted, so created a new post here.

Thanks for the solution to the above-linked problem.

But my database is large and complex in comparison to what I shared, hence it's not quite working for me. Also, it's not the case where values would appear only for Q1.

Can someone replace quarters with index number and send me the revised output according to below table.

1	428	0	428
2	0	1	429
3	431	3	431
4	0	2	433
5	0	0	433
6	435	1	435
7	0	2	437
8	0	4	441

Basically in Excel, column D would be: if(B2>0, B2, C2+D1)

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Expected result CC = 
VAR _table =
    ADDCOLUMNS (
        Data,
        "@condition",
            IF (
                Data[Value] <> 0
                    && MAXX ( FILTER ( Data, Data[Index] = EARLIER ( Data[Index] ) - 1 ), Data[Value] )
                        IN { 0, BLANK () },
                1,
                0
            )
    )
VAR _grouptable =
    ADDCOLUMNS (
        _table,
        "@group", SUMX ( FILTER ( _table, Data[Index] <= EARLIER ( Data[Index] ) ), [@condition] )
    )
VAR _resulttable =
    ADDCOLUMNS (
        _grouptable,
        "@result", IF ( Data[Value] > 0, Data[Value], Data[Add] )
    )
VAR _resultaccumulate =
    ADDCOLUMNS (
        _resulttable,
        "@accumulate",
            SUMX (
                FILTER (
                    _resulttable,
                    [@group] = EARLIER ( [@group] )
                        && Data[Index] <= EARLIER ( Data[Index] )
                ),
                [@result]
            )
    )
RETURN
    MAXX (
        FILTER ( _resultaccumulate, Data[Index] = EARLIER ( Data[Index] ) ),
        [@accumulate]
    )

[AdityaG]

Thanks for the solution. It seems to work, but if you don't mind can you just change the last 2 entries as the below number and see if the desired output comes up when you run the same code:

7  438  2   438

8  440  4   440

Again, apologies for not entering the table, as the replies are automatically getting deleted.

Edit: No worries, I found the small bug, that was giving erroneous error. The var _table should be just this:

var _table = addcolumns(data,"@condition",if(data[value]<>0,1,0))

Thanks a ton for the effort though. Really appreciate it.