How to perform 4 weeks rolling average for fiscal week?

[slavisha84]

Hello there, 

I am trying to create a line chart with a 4-week moving average but for fiscal weeks. 
I have two tables, one that contains my fiscal calendar with true dates, fiscal week, fiscal months, fiscal quarter, and fiscal week. 
The second table contains dates and revenue. 

What I need to produce is a line chart with 4 weeks moving average for revenue 2020 vs 2021. So this would be a chart with two lines where I need to plot 4 weeks moving average for 2020 and then 4 weeks moving average for 2021 so far.  

I tried creating a quick measure with a 4-week moving average. My code looked like this:

Revenue rolling average = 
IF(
	ISFILTERED('FiscalCalendar'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __LAST_DATE = LASTDATE('FiscalCalendar'[Date].[Date])
	RETURN
		AVERAGEX(
			DATESBETWEEN(
				'FiscalCalendar'[Date].[Date],
				DATEADD(__LAST_DATE, -28, DAY),
				DATEADD(__LAST_DATE, 1, DAY)
			),
			CALCULATE(SUM('RevenueByDate'[Revenue]))
		)
)

But this didnt work.

 have uploaded sample.pbix file and excel data into my one drive so you can download it here:
https://1drv.ms/u/s!AhhZq1add5YwjYIvuASi76lCL3R1eA?e=sZQshq

Can you please help me produce this chart with the data attached? 

Thank you in advance. 

Slav

[Jihwan_Kim]

Hi, @slavisha84 

I am not sure how your real model looks like, but please kindly check the below.

create a relationship between two tables.

assign fiscalcalendartable as DateTable.

create the below measure.

4W Moving Average Revenue =
VAR currentweek =
MAX ( FiscalCalendar[FiscalWeek] )
VAR period =
FILTER (
ALLSELECTED ( FiscalCalendar[FiscalWeek] ),
FiscalCalendar[FiscalWeek] > currentweek - 4
&& FiscalCalendar[FiscalWeek] <= currentweek
)
RETURN
AVERAGEX ( period, [Revenue] )

Other measure is also in the sample pbix file, link down below.

https://www.dropbox.com/s/va7lz4oz8bx0iff/Sample.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[slavisha84]

@Jihwan_Kim this is awesome. Almost there.

So what you did is close to what I need. I just need to separate 2020 and 2021 so i can plot it against each other on the same line chart. 
What you did is combined all 2020 and 2021 together. How do I go from here now?

I am assuming I will have to either create a column with a fiscal year or I will have to create a column with average revenue for 2020 and then another column with 2021 with average revenue. 

[Jihwan_Kim · Accepted solution]

Hi, @slavisha84 

Thank you for your feedback, and sorry that I made a mistake not to look into it carefully.

I revised it, and if I understand correctly, 4week moving average resets every year, right?

If you want no-reset, and continue it from the previous year as well, then need a slightly different measure.

Please check the below link and let me know if it is OK with you.

https://www.dropbox.com/s/va7lz4oz8bx0iff/Sample.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[slavisha84]

@Jihwan_Kim - This is great. And I am actually able to follow the logic here. This is what i was looking for. 
Now, I had to make two changes to my dataset. 

1. I had to delete records in the RevenueByDate table from today until the end of the year. 
2. I added the table with Forecast data for 2021. 

So after making an adjustment to data, when I plot the 4-week moving average on a line chart for 2020 and 2021, I am getting extra 3 weeks of data points for 2021 that I don't have data for yet. So the line for 2021 dips down. I am assuming this is because the function keeps running a 4-week moving average for the next 3 weeks even I don't have data? Is that correct? And how would i stop it from doing that and just run it until the current week?

And another question is, If I would need to add a 4-week rolling average for the forecasting data as well from the new table, how would I do that?

I updated the dataset at my shared folder:
https://1drv.ms/u/s!AhhZq1add5YwjYIvuASi76lCL3R1eA?e=sZQshq

[Jihwan_Kim]

Hi, @slavisha84 

Please try the below measures.

4W Moving Average Revenue =

VAR currentyear = MAX(FiscalCalendar[FiscalYear])

VAR currentweek =

MAX ( FiscalCalendar[FiscalWeek] )

VAR period =

FILTER (

ALLSELECTED ( FiscalCalendar[FiscalWeek], FiscalCalendar[FiscalYear] ),

FiscalCalendar[FiscalWeek] > currentweek - 4

&& FiscalCalendar[FiscalWeek] <= currentweek && FiscalCalendar[FiscalYear] = currentyear)

RETURN

IF( ISBLANK( [Revenue]), BLANK(), AVERAGEX ( period, [Revenue] ) )

Forecast =

SUMX(Forecast2021, Forecast2021[ForecastRevenue])

4W Moving Average Forecast =

VAR currentyear = MAX(FiscalCalendar[FiscalYear])

VAR currentweek =

MAX ( FiscalCalendar[FiscalWeek] )

VAR period =

FILTER (

ALLSELECTED ( FiscalCalendar[FiscalWeek], FiscalCalendar[FiscalYear] ),

FiscalCalendar[FiscalWeek] > currentweek - 4

&& FiscalCalendar[FiscalWeek] <= currentweek && FiscalCalendar[FiscalYear] = currentyear)

RETURN

IF( ISBLANK( [Forecast]), BLANK(), AVERAGEX ( period, [Forecast] ) )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[slavisha84]

@Jihwan_Kim  You are amazing. So this worked in terms of calculations but it is not letting me plot all 3 lines on the same graph.  I can plot them without problems on two different charts but i can't do it on one. 
Based on my research I think it is due to me using Legend to separate 2020 and 2021, so when i try to add 4W Moving Average Forecast as the second Secondary Value in the first chart, it is not accepting it. 

How do get this to work?

[amitchandak]

@slavisha84 , Create a week Rank on fiscal week and then you can get

new column

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format , Fiscal year week

Try measures like 

Last 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Refer my FY week video -https://youtu.be/euIC0dgGTNM

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8