Rolling 6 month average with non-consecutive dates

[Anonymous]

Hello,

I'm trying to build a 6-month rolling average of Sales $.

However, my sales data is aggregated by Week Starting Date, so the usual formulas don't seem to work. 

I currently have something that just adds up sales $ from the last 6 month numbers, but for every year. I understand why this doesn't work, but it's the closest I've come to a solution. I've also tried modifying the solution presented in this post with no luck https://community.powerbi.com/t5/Desktop/How-to-create-rolling-averages-over-non-consecutive-dates/m...

Any suggestions would be greatly appreciated.

Current formula:

Sales AVG 6M =

VAR MonthsInRange = 6

VAR LastMonthRange =

MAX (Dates[YYYYM#])

VAR FirstMonthRange =

LastMonthRange - MonthsInRange + 1

VAR Period6M =

FILTER (

ALL ( Dates[YYYYM#] ),

Dates[YYYYM#] >= FirstMonthRange

&& Dates[YYYYM#] <= LastMonthRange

)

VAR Result =

IF (

COUNTROWS ( Period6M ) >= MonthsInRange,

CALCULATE (

AVERAGEX ( Period6M, [Sales $_sample] ),

REMOVEFILTERS ( 'Dates' )

)

)

RETURN

Result

Here is a sample of the data and the result comlumn I want:
I would like each week to be the average of the 6 months/26 weeks before it.

[littlemojopuppy · Accepted solution]

@Anonymous do you have a date table and it marked as one?  Because if you do using DATESINPERIOD and referencing the date field in your date table should cover this.

[Anonymous]

@littlemojopuppy thank you! The date table was not marked correctly

[littlemojopuppy]

@Anonymous glad it was something easy!  🙂