Forum Discussion

brockry1's avatar
brockry1
Helper II
1 year ago
Solved

Dax Command for Rolling 3 Week Average

I'm looking to calculate a rolling 3 week average on my GSV for the last 3 weeks but need it to lag a week.  For the screen shot I would want an average for weeks 10,9 and 8 and when 11 is completed ...
  • techies's avatar
    techies
    1 year ago

    Hi brockry1 please check this

     

    Rolling_3_Week_Avg =
    VAR CurrentWeek = MAX('Sales'[Sales Week])
    VAR LastCompletedWeek = CurrentWeek - 1


    VAR AvailableWeeks =
    TOPN(3,
    FILTER(ALL('Sales'), 'Sales'[Sales Week] <= LastCompletedWeek),
    'Sales'[Sales Week], DESC
    )

    VAR AvgGSV =
    AVERAGEX(AvailableWeeks, 'Sales'[GSV])

    RETURN
    AvgGSV

Jihwan_Kim's avatar
Jihwan_Kim
Super User
1 year ago

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

One of ways to create a measure for finding out recent three-complete-weeks rolling average is using WINDOW DAX function in the measure.

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

 

 

 

GSV measure: = 
SUM( data[gsv] )

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

complete 3 weeks rolling average: =
VAR _today =
    TODAY ()
VAR _currentweekenddate =
    MAXX (
        FILTER ( 'calendar', 'calendar'[date] = _today ),
        'calendar'[End of Week]
    )
VAR _completeweek = _today = _currentweekenddate
RETURN
    IF (
        _completeweek,
        AVERAGEX (
            WINDOW (
                1,
                ABS,
                3,
                ABS,
                FILTER (
                    ALL ( 'calendar'[End of Week] ),
                    'calendar'[End of Week] <= _currentweekenddate
                ),
                ORDERBY ( 'calendar'[End of Week], DESC )
            ),
            [GSV measure:]
        ),
        AVERAGEX (
            WINDOW (
                1,
                ABS,
                3,
                ABS,
                FILTER (
                    ALL ( 'calendar'[End of Week] ),
                    'calendar'[End of Week] < _currentweekenddate
                ),
                ORDERBY ( 'calendar'[End of Week], DESC )
            ),
            [GSV measure:]
        )
    )

 

  • brockry1's avatar
    brockry1
    Helper II
    1 year ago

    So I do have a Calendar Table:

    And have a many to one relationship on date to date for my Fact Table to Calendar. 

    So the end of week in the sample isn't working. 

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      1 year ago

      Hi,

      I still need to understand how your calendar dimension table is structured, for instance, what column is sorted by what column.

      Please share your sample pbix file's link, and then, I can try to look into it.

      Thank you.