Dax Command for Rolling 3 Week Average

[brockry1]

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 and 12 starts showing I would want 11, 10 and 9 and so on each time a new week starts.  In my data table I brought in the year, period and week.  I did not bring in date but if needed I can.  

Distribution Type	Period	Week	GSV
Vetsource	P02	5	645
Vetsource	P02	6	635
Vetsource	P02	7	490
Vetsource	P02	8	718
Vetsource	P03	9	689
Vetsource	P03	10	622
Vetsource	P03	11	98

[v-hashadapu]

Hi @brockry1 ,
I hope the information shared was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you!

[v-hashadapu]

Hi @brockry1 , Just checking in—were you able to resolve the issue?
If one of the replies helped, please consider marking it as "Accept as Solution" and giving a 'Kudos'. Doing so can assist other community members in finding answers more quickly.
Thank you!

[v-hashadapu]

Hi @brockry1 , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
Thank you.

[techies · Accepted solution]

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]

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]

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]

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.