topic Re: Reply to earlier post on calculated column from two col. to add previous rows only if 1st col. is 0 in DAX Commands and Tips

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

AdityaG — Sun, 14 May 2023 09:30:25 GMT

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculated-column-from-two-columns-to-add-previous-rows-only-if/m-p/3234158#M118780

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)

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

Jihwan_Kim — Sun, 14 May 2023 12:23:47 GMT

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] )

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

AdityaG — Sun, 14 May 2023 15:58:54 GMT

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.