Finding the maximum value within a 12 month date range

[onedayover]

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

[onedayover]

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

[Jihwan_Kim · Accepted solution]

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