topic Maximum value in dynamic date range in DAX Commands and Tips

Maximum value in dynamic date range

zolotrip — Fri, 31 Dec 2021 08:13:09 GMT

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))

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.

Re: Maximum value in dynamic date range

amitchandak — Fri, 31 Dec 2021 08:21:30 GMT

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])

Re: Maximum value in dynamic date range

ValtteriN — Fri, 31 Dec 2021 08:30:14 GMT

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:

End result:

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

Re: Maximum value in dynamic date range

zolotrip — Fri, 31 Dec 2021 09:23:29 GMT

Dear amitchandak:

Thank you for your quick response. It's working perfectly.

Best regards.

Re: Maximum value in dynamic date range

zolotrip — Fri, 31 Dec 2021 09:34:57 GMT

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.

Re: Maximum value in dynamic date range

ValtteriN — Fri, 31 Dec 2021 10:53:35 GMT

Hi,

You can either use MAX it often works in a similar way or this pattern: IF(HASONEVALUE([column]), VALUES([column]), [alternateresult]).