Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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
Hi, @slavisha84
Please try the below measures.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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?
@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
User | Count |
---|---|
85 | |
85 | |
67 | |
61 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |