Forum Discussion
Creating new column values based on previous rows value
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?
6 Replies
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/339586
But how do we select the number of earlier observations? I have also added the sample data.
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]).
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 _ EasonI have added the details about the expected result. Please let me know if it still not clear. Waiting for your positive reply.
