How to Add Calculated Column that shows value of another column based on value of previous row

[AndreaPiscitell]

Hi everyone, 

i tried to look for a solution and cannot get it anywhere. 

I have a table and i am trying to add a column 'brand' which contains a specific value from the 'text' column.

The only way that the correct value is added is that the previous row contains the value "I'll need to know your brand first".  Not sure how to do this. Please see example below.

Table:

date	session	user	text	Expected brand
10/10/22 5:47	Session1	User1	Welcome!
10/10/22 5:48	Session3	User1	Setup device
10/10/22 5:48	Session3	User1	I'll need to know your brand first.
10/10/22 5:48	Session3	User1	BrandB	BrandB
10/10/22 5:49	Session3	User1	Press keys
10/10/22 5:52	Session3	User1	Text sample
10/10/22 5:52	Session3	User1	Great!
10/10/22 5:52	Session4	User1	Setup device
10/10/22 5:52	Session4	User1	I'll need to know your brand first.
10/10/22 5:53	Session4	User1	BrandA	BrandA
10/10/22 5:53	Session4	User1	Press keys
10/10/22 5:53	Session4	User1	Text sample
10/10/22 5:53	Session4	User1	Great!
20/10/22 19:30	Session6	User2	Welcome!
20/10/22 19:30	Session6	User2	I'll need to know your brand first.
20/10/22 19:30	Session6	User2	BrandC	BrandC
20/10/22 19:30	Session6	User2	Text sample
20/10/22 19:31	Session6	User2	Text sample
20/10/22 19:31	Session6	User2	Text sample
20/10/22 19:31	Session6	User2	Try again
20/10/22 19:35	Session7	User2	I'll need to know your brand first.
20/10/22 19:35	Session7	User2	BrandD	BrandD

I hope you can help me out, i would much appreciate that.

Regards,

A

[m_alireza · Accepted solution]

Hi @AndreaPiscitell ,


1) Create an index column in Power Query
2) Create this calculated column:

 VAR __Previous = MAXX(FILTER('Table',[index]<EARLIER('Table'[index])),[index])

    VAR __PreviousText = MAXX(FILTER('Table',[index]=__Previous),[text])
RETURN
    IF(__PreviousText ="I'll need to know your brand first.", 'Table'[text])

Sample output:



I found an old post that sort of had a similar issue to yours: https://community.powerbi.com/t5/Desktop/Custom-column-evaluating-condition-of-previous-row/td-p/967...

Based on the discussions from there, I came up with the above. Hope it helps.