How to Show Average Last X Weeks in a Visual?

[Natasha93]

Hi 🙂

I have a clustered bar chart to show a count of cases created according to date by location.

I would like the visual to show the average cases from the last 4 weeks.

I am wracking my brain around Time Intelligence DAX, but can't find anything definitive that works!

Any help will be much appreciated!

[MisterFry · Accepted solution]

To solve these time intelligence issues, I maintain a calendar table that has a field that I call 'Week Offset Number'. The file contains an integer value describing the number of weeks into the past the indicated week is. (last week is -1, the week prior is -2). 
This makes the DAX to look at the 'last x weeks' or even the 'last x weeks from the selected week' DAX pretty easy. 

Average Measure = CALCULATE(AVERAGE(<FACT>), ALL(Calendar), Calendar[Week Offset Num] > -4)

[lbendlin]

You have a Calendar table that includes week numbers. Define what you mean by "last 4 weeks"  (does it include the current week or not?) and then write your measure accordingly. Show what you have tried.