Rolling 5 weeks average

[Venkata1858]

Hi,

I'm new to power bi, I need to calculate rolling 5 weeks average for my weekly sales data, I tried in multiple ways but I'm getting wrong output can some one help me please. below is the data I have.

Date Dimension :

Fact table: 

Thanks in advance.

[Anonymous]

Ashish/Venkata, would this solution work if you didn't have sales in every week but wanted to calculate rolling average regardless? Thank you

[Ashish_Mathur]

Hi,

Blanks are ignored when calculating averages.

[Ashish_Mathur · Accepted solution]

Hi,

Download the PBI file from here.

Hope this helps.

[Venkata1858]

@Ashish_Mathur, May I know how this DAX works 

5 weeks rolling average sales = if(isblank([Sales]),blank(),AVERAGEX(DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date])-34,Min('Calendar'[Date])),[Sales]))

[Ashish_Mathur]

Please read up on the AVERAGEX() and DATESBETWEEN() functions.  You'll then be able to understand the formula.

[Venkata1858]

Sure Ashish

[Venkata1858]

Hi @Ashish_Mathur , Thanks a  lot it is working perfect, your response was quicker than escape velocity.

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.