Forum Discussion

wyanjaspew's avatar
wyanjaspew
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Rolling values for 3 months

Hi Guys, Could you help me configure this? I'm trying to display the rolling 3-month period using the following DAX code. I’d like the output to look like this.  
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    One of ways is to use WINDOW DAX Function in calculated table formula.

    Please check the below picture and the attached pbix file.

     

    WINDOW function (DAX) - DAX | Microsoft Learn

     

     

     

     

    Calculated Column expected result = 
VAR _t =
    WINDOW (
        -2,
        REL,
        0,
        REL,
        test,
        ORDERBY ( test[date], ASC ),
        ,
        ,
        MATCHBY ( test[date] )
    )
VAR _condition =
    COUNTROWS ( _t ) = 3
RETURN
    IF ( _condition, AVERAGEX ( _t, test[value] ) )

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
1 year ago

Hi,

One of ways is to use WINDOW DAX Function in calculated table formula.

Please check the below picture and the attached pbix file.

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

 

 

 

Calculated Column expected result = 
VAR _t =
    WINDOW (
        -2,
        REL,
        0,
        REL,
        test,
        ORDERBY ( test[date], ASC ),
        ,
        ,
        MATCHBY ( test[date] )
    )
VAR _condition =
    COUNTROWS ( _t ) = 3
RETURN
    IF ( _condition, AVERAGEX ( _t, test[value] ) )

 

  • wyanjaspew's avatar
    wyanjaspew
    Icon for Advocate I rankAdvocate I
    1 year ago

    Thanks! I'm trying to incorporate the code into a measure within my actual dataset, but it doesn't seem to be functioning properly.

     

    Actual dataset