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.
Hi
I am going crazy trying to figure this out. I want to create a dynamic 4 week trailing average of the number of orders that our company received. My goal is to have a column bar chart with orders and then a line with the 4 week trailing orders average.
There are two other conditions: 1) I have a column of week ending dates all on Fridays that I need to use 2) I have another column with two options: 1) Ordered 2) Canceled - I only need Ordered.
I have watched over 10 videos and going through the message board and can't figure it out. Screenshots below:
Solved! Go to Solution.
@BabyBinki821 , Create a rank on week ending date
new column
week rank = rankx(Date, Date[Week End],,asc,dense)
First measure
orders = countrows(filter(Table, Table[Order Status] ="Ordered"))
4 week rolling
Last 4 weeks = CALCULATE([orders ], FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Avg 4 week rolling
Avg Last 4 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [orders ]) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
prefer a date table
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
@BabyBinki821 , Create a rank on week ending date
new column
week rank = rankx(Date, Date[Week End],,asc,dense)
First measure
orders = countrows(filter(Table, Table[Order Status] ="Ordered"))
4 week rolling
Last 4 weeks = CALCULATE([orders ], FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Avg 4 week rolling
Avg Last 4 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [orders ]) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
prefer a date table
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
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
38 | |
21 | |
20 | |
13 |
User | Count |
---|---|
125 | |
41 | |
39 | |
26 | |
24 |