Forum Discussion

onedayover's avatar
onedayover
Icon for Helper III rankHelper III
4 years ago
Solved

Finding the maximum value within a 12 month date range

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 ex...
Max value in date range
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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