Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LaurenceSD
Advocate II
Advocate II

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 

Bkgs TY 4 Wk =
CALCULATE([Bkgs TY],
FILTER(Dates,Dates[Date]>=(today()-1)-(4*7)),FILTER(Dates,Dates[Date]<=(today()-1)))
 
But when I changed the month in the slicer, this value changed, I tried adding in an all filter, but it didn't change it 
 
Bkgs TY 4 Wk =
CALCULATE([Bkgs TY],
all(Dates),FILTER(Dates,Dates[Date]>=(today()-1)-(4*7)),FILTER(Dates,Dates[Date]<=(today()-1)))
 
Any pointers on where I'm going wrong would be greatly appreciated
1 ACCEPTED 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

 

LaurenceSD_0-1621418047226.png

 

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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.

 

LaurenceSD_0-1621414349543.png

 

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

 

LaurenceSD_1-1621414457829.png

 

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

 

LaurenceSD_0-1621418047226.png

 

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors