topic Finding the maximum value within a 12 month date range in DAX Commands and Tips

Finding the maximum value within a 12 month date range

onedayover — Wed, 22 Jun 2022 01:44:45 GMT

Hi All

I need help in creating a new column in my table (MaxValueWithinRange) where the value returned is the maximum value within a 12 month date range from a corresponding date in the row.

For example, if the date (in the "Date" column) is 17/09/2021, the date range I would want to look up the max value from would be 18/09/2020 to 17/09/2021. The max value within this range is 17407.

Please see example below of what I'm trying to achieve. Please note, the "From" and "To" columns are only in this example and not actually in my PBI table.

Many thanks
darls15

Re: Finding the maximum value within a 12 month date range

Jihwan_Kim — Wed, 22 Jun 2022 03:29:48 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pibx file.

It is for creating a new column.

MaxValueWithinRange CC = VAR _rangeenddate = Data[Date] VAR _rangestartdate = DATE ( YEAR ( _rangeenddate ) - 1, MONTH ( _rangeenddate ), DAY ( _rangeenddate ) ) + 1 VAR _result = MAXX ( FILTER ( Data, Data[Date] >= _rangestartdate && Data[Date] <= _rangeenddate ), Data[TotalUsers] ) RETURN _result

Re: Finding the maximum value within a 12 month date range

onedayover — Wed, 22 Jun 2022 03:45:21 GMT

Yes that's exactly it! It works perfectly with my data, thank you so much Jihwan_Kim 😊