Creating new column values based on previous rows value

[Dunner2020]

Hi there,

I have a table that contains 3 columns (index, Waste Value, Running total of waste value). I want to create or populate new column/measure  (let say called Replaced Value) in such a way that if Running Total value (which is cumulative sum of 48 values of column named waste value) exceeds a certain threshold (let say 150), then it checks 48 past values of the column  'waste value'  and sees if the waste column value is greater than 10 then put 10 in Replaced value column otherwise copy the same value of 'waste value' column into Replaced Value column. Here is how the structure of the table looks like:

Date Time	Waste Value	Running Total	Replaced Value
1/01/2000 0:00	2		2
1/01/2000 0:30	3		3
1/01/2000 1:00	4		4
1/01/2000 1:30	5		5
1/01/2000 2:00	6		6
1/01/2000 2:30	7		7
1/01/2000 3:00	8		8
1/01/2000 3:30	9		9
1/01/2000 4:00	0		0
1/01/2000 4:30	1		1
1/01/2000 5:00	2		2
1/01/2000 5:30	13		10
1/01/2000 6:00	14		10
1/01/2000 6:30	16		10
1/01/2000 7:00	11		10
1/01/2000 7:30	2		2
1/01/2000 8:00	2		2
1/01/2000 8:30	8		8
1/01/2000 9:00	9		9
1/01/2000 10:00	0		0
1/01/2000 10:30	4		4
1/01/2000 11:00	8		8
1/01/2000 11:30	9		9
1/01/2000 12:00	0		0
1/01/2000 12:30	11		10
1/01/2000 13:00	2		2
1/01/2000 13:30	3		3
1/01/2000 14:00	4		4
1/01/2000 14:30	5		5
1/01/2000 15:00	6		6
1/01/2000 15:30	7		7
1/01/2000 16:00	8		8
1/01/2000 16:30	9		9
1/01/2000 17:00	22		10
1/01/2000 17:30	3		3
1/01/2000 18:00	21		10
1/01/2000 18:30	1		1
1/01/2000 19:00	2		2
1/01/2000 19:30	8		8
1/01/2000 20:00	7		7
1/01/2000 20:30	6		6
1/01/2000 21:00	5		5
1/01/2000 21:30	4		4
1/01/2000 22:00	3		3
1/01/2000 22:30	2		2
1/01/2000 23:00	9		9
1/01/2000 23:30	6	297	6
1/02/2000 0:00	5	300	5
1/02/2000 0:30	4	301	4
1/02/2000 1:00	3	300	3
1/02/2000 1:30	6	301	6
1/02/2000 2:00	8	303	8
1/02/2000 2:30	11	307	10
1/02/2000 3:00	12	311	10

For example, Running total value exceeds 150 when Date Time value is '1/01/2000 23:30'. To generate 'Replaced Value' column, it starts looking back 48 values of waste value column, if the value of the waste column is more than 10, then the value of Replaced Value column would be 10 (as shown in blue color) otherwise it would be same as the waste column is. I hope that clarifies the confusion. 

Could anyone guide me how can we do in power BI?

[v-easonf-msft]

Hi ， @Dunner2020 

The calculation logic is still not clear.

Can your share more details about your excepted result?

And  it will be convenient   for us to understand  your  question  and check  the result of  our soulution.

Best Regards,
Community Support Team _ Eason

[Dunner2020]

Hi @v-easonf-msft 

I have added the details about the expected result. Please let me know if it still not clear. Waiting for your positive reply.

[Greg_Deckler]

Sample source data as text would aid tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, dealing with previous values in rows generally involves variables or EARLIER. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[Dunner2020]

But how do we select the number of earlier observations? I have also added the sample data.

[amitchandak]

Question is not clear, But you can try like this. Both are new columns

Last Date Time = maxx(filter(table,table[Date Time]<earlier(table[Date Time])),table[Date Time])
Last value = maxx(filter(table,table[Date Time] =earlier(table[Last Date Time])),table[value])

. 

[Dunner2020]

Hi @amitchandak 

Sorry for the confusion, I have added more explanation. Hopefully that make sense now.