cancel
Showing results 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

## 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

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 3
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

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?

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

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

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors