Forum Discussion
Max values for last rows
Hi All
I have a table with date/time and stock price. In total the table has 100000 rows. What I am trying to do is to find the max value of the last 10 Rows for each Row.
| Date/Time | price |
| 22.04.2021 16:44 | 61.27 |
| 22.04.2021 16:45 | 61.26 |
| 22.04.2021 16:46 | 61.26 |
| 22.04.2021 16:47 | 61.2 |
| 22.04.2021 16:48 | 61.24 |
| 22.04.2021 16:49 | 61.24 |
| 22.04.2021 16:50 | 61.25 |
| 22.04.2021 16:51 | 61.22 |
| 22.04.2021 16:52 | 61.21 |
| 22.04.2021 16:53 | 61.22 |
In Excel that would be easy and would look like:
But how to do that in Power BI?
All the Data is stored in a single table.
Thanks in Advanced!
Anonymous ,
Try measure like
calculate(max(Table[price]), filter(all(Table), Table[Date] >= max(Table[Date]) -10))
or
calculate(max(Table[price]), filter(all(Table), Table[stock] = max(Table[Stock]) && Table[Date] >= max(Table[Date]) -10))
3 Replies
Anonymous ,
Try measure like
calculate(max(Table[price]), filter(all(Table), Table[Date] >= max(Table[Date]) -10))
or
calculate(max(Table[price]), filter(all(Table), Table[stock] = max(Table[Stock]) && Table[Date] >= max(Table[Date]) -10))I have been looking this solution up for quite a long time, almost everywhere on the internet, unsuccesfully, so far.
This time I thought it would be different, as the problem decribed pretty matches mine, but this solution doesn't look to work properly for me.
The solutions gets the same result for each row. I believe this is because it is always getting de max in the very last 10 days, always the same last ten days in the whole series.
I would need something "dynamic", let's say. In the sense that I need to have the max value of the last ten days in the context of every row. Just like this:
Well, in this example it's the max in the last 3 days, in the context of every row.
calculate(max(Table[price]), filter(all(Table), Table[Date] >= max(Table[Date]) -10))Or
calculate(max(Table[price]), filter(all(Table), Table[stock] = max(Table[Stock]) && Table[Date] >= max(Table[Date]) -10))
None of the two solutions provided are working for me. I am writting the DAX formulas on Power Pivot, but I guess that's not the reason.
Any ideas?
Thanks in advance.
