Forum Discussion
Fixed Last 4 weeks calculation
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
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?
Bookings This Year Last 4 Week By Day =CALCULATE([Bookings This Year],FILTER(Dates,Dates[Date]>=(today()-1)-(4*7)),FILTER(Dates,Dates[Date]<=(today()-1)))Bookings This Year Last 4 Week Total = CALCULATE([Bookings This Year Last 4 Week By Day],ALL(Dates))Thanks
3 Replies
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 formatTry 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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8thanks, 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?
Bookings This Year Last 4 Week By Day =CALCULATE([Bookings This Year],FILTER(Dates,Dates[Date]>=(today()-1)-(4*7)),FILTER(Dates,Dates[Date]<=(today()-1)))Bookings This Year Last 4 Week Total = CALCULATE([Bookings This Year Last 4 Week By Day],ALL(Dates))Thanks
