Forum Discussion

wyanjaspew's avatar
wyanjaspew
Advocate 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] ) )

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super 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
      Advocate 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