Forum Discussion
Solved
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", le...
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 return
CALCULATE(MAX(MaxLast[Value]),ALL('MaxLast'[Date]),DATESBETWEEN('calendar'[Date],Cdate,Cdate+3))
Test data:
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!
Dear ValtteriN :
I don't have available the SELECTEDVAUE expression in Power Pivot, I'm afraid.
Do you know an equivalent to it?
Thank you so much.
Hi,
You can either use MAX it often works in a similar way or this pattern: IF(HASONEVALUE([column]), VALUES([column]), [alternateresult]).