Forum Discussion
Maximum value in dynamic date range
Hi there.
It might sound simple, but I have been looking this solution up for quite a long time, almost everywhere on the internet, unsuccesfully, so far.
I would need something "dynamic", let's say, in the sense that I need to have the max value in the last 3 days, in the context of every row. Just like this:
calculate(max(Table[value]), filter(all(Table), Table[date] >= max(Table[date]) -3))
Or
calculate(max(Table[value]), filter(all(Table), Table[value] = max(Table[value]) && Table[date] >= max(Table[date]) -3))
None of the two solutions shown above are working for me.
The first one is giving "25" for every row. The second one is giving blank for every row.
I am writting the DAX formulas on Power Pivot, but I guess that's not the reason.
Any ideas?
Many thanks to everyone.
zolotrip , Please find code for new column and measure
new column =
maxx(filter(Table, Table[date] >= earlier(Table[Date]) -3 && Table[date] <= earlier(Table[date])),[Table[value])
new measure =
maxx(filter(allselected(Table), Table[date] >= max(Table[Date]) -3 && Table[date] <= max(Table[date])),[Table[value])
5 Replies
zolotrip , Please find code for new column and measure
new column =
maxx(filter(Table, Table[date] >= earlier(Table[Date]) -3 && Table[date] <= earlier(Table[date])),[Table[value])
new measure =
maxx(filter(allselected(Table), Table[date] >= max(Table[Date]) -3 && Table[date] <= max(Table[date])),[Table[value])Hi,
This pattern ought to do what you want:MaxLast3 =var Cdate =SELECTEDVALUE('calendar'[Date])-3 returnCALCULATE(MAX(MaxLast[Value]),ALL('MaxLast'[Date]),DATESBETWEEN('calendar'[Date],Cdate,Cdate+3))
Test data:
End result:
I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!Hi,
You can either use MAX it often works in a similar way or this pattern: IF(HASONEVALUE([column]), VALUES([column]), [alternateresult]).
