topic Re: Calculated column from two columns to add previous rows only if first column value is 0 in DAX Commands and Tips

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

AdityaG — Sun, 14 May 2023 06:47:13 GMT

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!

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

Jihwan_Kim — Sun, 14 May 2023 08:05:39 GMT

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

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

AdityaG — Sun, 14 May 2023 11:54:42 GMT

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)

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

Jihwan_Kim — Sun, 14 May 2023 12:01:12 GMT

Hi,

Thank you for your message.

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

Thanks.

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

AdityaG — Sun, 14 May 2023 12:03:45 GMT

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Reply-to-earlier-post-on-calculated-column-from-two-col-to-add/m-p/3234196#M118793

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

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

tamerj1 — Sun, 14 May 2023 15:46:09 GMT

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.

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

AdityaG — Sun, 14 May 2023 15:55:18 GMT

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!

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

tamerj1 — Sun, 14 May 2023 16:08:39 GMT

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.