Forum Discussion

LaurenceSD's avatar
LaurenceSD
Advocate II
5 years ago
Solved

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 forecas...
Need Help
  • LaurenceSD's avatar
    LaurenceSD
    5 years ago

    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
amitchandak's avatar
amitchandak
Super User
5 years ago

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

LaurenceSD's avatar
LaurenceSD
Advocate II
5 years ago

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

  • LaurenceSD's avatar
    LaurenceSD
    Advocate II
    5 years ago

    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