cancel
Showing results for
Did you mean:
Post Partisan

## rolling average without time intelligence

I have a measure ([Total Sales]) that I want to compute a 6-week rolling average on.  The "date" grain of my data model is week.  So, I am unable to use the quick measure feature to auto-generate a rolling average.  My week labeling is unusual (e.g., "FY 1 W1"); this is stored in column [Week Label].  So, I have created a [Week Number] column to get the proper ordering.  My newest [Week Number] is 52; my oldest [Week Number] is 1.  I have tried the following in a visual with axis [Week Label], but it's returning the [Total Sales] for each week, not the 6-week rolling average.  How can I resolve this?

``````Rolling 6 Weeks Average of Total Sales =

//get the given week
VAR __given_week =
SELECTEDVALUE('Dimension Week'[Week Number])

//calculate total sales for each week
VAR __table =
SUMMARIZE(
'Dimension Week'
,'Dimension Week'[Week Number]
)
,"Total Sales Value"
,[Total Sales]
)

RETURN
AVERAGEX(
FILTER(
__table
,[Week Number] <= __given_week
&& [Week Number] > __given_week - 6
)
,[Total Sales]
)``````

1 ACCEPTED SOLUTION
Post Partisan

I solved my problem with the following:

``````Rolling 6 Weeks Average of Total Sales =

//get the given week
VAR __given_week =
SELECTEDVALUE('Dimension Week'[Week Number])

//number of weeks in rolling average
VAR __duration =
6

//create a table of weekly ranges per week
VAR __calculation_range =
FILTER(
ALL('Dimension Week')
,[Week Number] <= __given_week
&& [Week Number] > __given_week - __duration
)

RETURN
IF(
COUNTROWS(__calculation_range) = __duration //only return the result when there are the desired number of weeks in the range (e.g., week 5 doesn't have 6 weeks in it)
,CALCULATE(
AVERAGEX(
'Dimension Week'
,[Total Sales]
)
,__calculation_range
)
,BLANK()
)``````
2 REPLIES 2
Community Support

Hi, @qubit813

Glad to hear that you have solved your problem by yourself and thank you for the experience sharing.😊

Would you like to mark your own reply as a solution so that others can learn from it too?

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Post Partisan

I solved my problem with the following:

``````Rolling 6 Weeks Average of Total Sales =

//get the given week
VAR __given_week =
SELECTEDVALUE('Dimension Week'[Week Number])

//number of weeks in rolling average
VAR __duration =
6

//create a table of weekly ranges per week
VAR __calculation_range =
FILTER(
ALL('Dimension Week')
,[Week Number] <= __given_week
&& [Week Number] > __given_week - __duration
)

RETURN
IF(
COUNTROWS(__calculation_range) = __duration //only return the result when there are the desired number of weeks in the range (e.g., week 5 doesn't have 6 weeks in it)
,CALCULATE(
AVERAGEX(
'Dimension Week'
,[Total Sales]
)
,__calculation_range
)
,BLANK()
)``````

Announcements