Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I'm trying to create a measure that'll show me the Last for weeks value this year and the last 4 weeks value the year before, regardless of the dates specified (I'm wanting to do some forecasting based on performance over the last 4 weeks coupled with further sales done in prior years, so I don't want it to flex when the dates move along the axis or when any date slicers are applied).
The formula I came up with was
Solved! Go to Solution.
Right I've now found a solution
The column Bookings This Year Last 4 Week Total in the below was what i was wanting to acheive, I can now plug in the same formula to get Last Year Minus one and then work out the growth
I've had to use two formulas to acheive this, I'm sure there must be a way of making this into one formula? Is that possible?
@LaurenceSD , Create following column in date table
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
Try measure like - Example
Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Also, make sure [Bkgs TY] is not using the time intelligence function already
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
thanks, @amitchandak that formula and new columns do work, but not quite how I was hoping, because If I plot that on a graph with dates on the axis, the amounts vary week on week and so when it gets to the future, it eventually goes down to 0.
What I need is to do is calculate the last 4 weeks bookings and the last 4 weeks booking a year ago, then workout the growth over last year and then apply that % to the further sales achieved last year. So this % needs to be fixed in no matter what the date. The Bookings This Year Projection is the line i'm trying to amend.
So my Projection is based on whats been done already + what was done for the rest of last year X by the growth over the last 4 weeks & then putting this into a running total formula
Above is the formula and for demonstrating the concept i've put in 0.9, so reducing last years further bookings by 10%, so saying we've been doing 10% worse over the last 4 weeks. So it's this % figure i need to replace with that 4 week calculation.
Does this make sense?
Thanks
Laurence
Right I've now found a solution
The column Bookings This Year Last 4 Week Total in the below was what i was wanting to acheive, I can now plug in the same formula to get Last Year Minus one and then work out the growth
I've had to use two formulas to acheive this, I'm sure there must be a way of making this into one formula? Is that possible?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.