Calculated column from two columns to add previous rows only if first column value is 0

[AdityaG]

Hi community,

I have the following problem to be calculated in PowerBI, either through DAX or power query.

Q1-23	428	0	428
Q2-23	0	1	429
Q3-23	0	0	429
Q4-23	0	2	431
Q1-24	434	2	434
Q2-24	0	0	434
Q3-24	0	4	438
Q4-24	0	3	441

The second and third are input columns, and the fourth one is output. 

In Excel, the fourth column is prepared as:

if(B2>0, B2, C2+D1)

Please let me know if its possible in PowerBI. Thanks!

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Expected result CC =
VAR _result =
    SUMX (
        FILTER (
            ADDCOLUMNS ( Data, "@total", IF ( Data[Value] <> 0, Data[Value], Data[Add] ) ),
            RIGHT ( Data[Quarter-Year], 2 ) = RIGHT ( EARLIER ( Data[Quarter-Year] ), 2 )
                && RIGHT ( LEFT ( Data[Quarter-Year], 2 ), 1 )
                    <= RIGHT ( LEFT ( EARLIER ( Data[Quarter-Year] ), 2 ), 1 )
        ),
        [@total]
    )
RETURN
    _result

[AdityaG]

Can you consider taking index numbers instead of quarters.... and try if its possible to replicate the Excel function mentioned above: if(B2>0,B2,C2+D1)

[tamerj1]

Hi @AdityaG 

Thank you for posting your question and for being a regular visitor to this platform.
I would kindly request you to advise what exactly is the issue with @Jihwan_Kim's solution and avoid providing guiding instructions that the solution provider cannot just follow blindly. In other words it would be really appreciated if you provide clear picture of you data model and the expected results rather than trying to over simplify the problem. Allways remember that the solution provider has his own methods and approaches, therefore providing guidance on how to obtain the expected results will only create confusion. Appreciate your understanding. 

[AdityaG]

Hi @tamerj1 , I have created another post and sent the link (please check the subsequent replies as well). The problem is this interface is too buggy, it automatically deletes the replies whenever I'm adding sample data, and sometimes it does not accept the message due to an invalid HTML error. I try to be very exhaustive with what the problem is (you can see that in the link I sent to him as well).

Apologies from my side, and let me know how can I avoid such errors (if there's a thread made on this, would really appreciate it if you could share the link). I'm kinda new to this community too.

Thanks!

[tamerj1]

@AdityaG 

Sorry that you had to face such technical issues. Please do not misunderstand my message. This is just to help supporting you in the best way. 
Please let me know if your problem has been sorted our or you still need any support. 

[Jihwan_Kim]

Hi,

Thank you for your message.

Please provide a sample with index numbers, and then I can try to look into it.

Thanks.

[AdityaG · Accepted solution]

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Reply-to-earlier-post-on-calculated-column-fr...

Can you check this link, somehow replies are getting deleted as soon as I add a table