98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » Fixed Last 4 weeks This year and last year | Forum
Fixed Last 4 weeks This year and last year | Forum
Resolved · High Priority · Version 365
Wed 19 May 2021, 08:12Delegate Laurence said...
Laurence has attended:
Excel PowerPivot course
Fixed Last 4 weeks This year and last year
Hi,
I'm trying to figure out creating a measure that'll calculate the last 4 weeks bookings. I've got a bookings measure. But it needs to be a fixed last 4 weeks, so that if it was plotted on a graph with dates it would be a constant line and if any date slicers are applied it remains the same value.
What I'm trying to do is forecase sales for the rest of the month, based on the growth of the last 4 weeks this year, vs last 4 weeks last year (or year before as last year was a washout) & what was done for the rest of the month last year.
All the rest i think i'm fine with except for this last 4 weeks. Any help is appreciated
Thanks
Thu 20 May 2021, 11:28Trainer Jens said...
RE: Fixed Last 4 weeks This year and last year
Hi Laurence,
Thank you for the forum question.
I do not know your data model, the names of your tables, or headings.
I assume that you have a date table in your model to be able to work with date intelligence.
The two DAX measures below should both return last 4 weeks number of bookings, but you will need to change the table names and column names to the table names and column names you have in your model.
'Table'[Date] needs to be replace with the name of your date table and the name of the column of your primary key in the date table.
Count Bookings 28DaysAgo = CALCULATE(Count(Bookings),DATEADD('Table'[Date],-28,DAY))
Last4weeksBookings=
CALCULATE (
[Count of bookings],
DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -28, DAY )
)
If you want to compare periods 2 years ago you will need something like:
calculate(measure, parallelperiod('Table'[Date], -24, month))
And if you don't want the count of last 4 weeks bookings to be filter when you filter by date slicers, you will need to apply the All function to the measure.
Count Bookings 28DaysAgo = CALCULATE(Count(Bookings),DATEADD('Table'[Date],-28,DAY),All("The name of your date table"))
I hope this can guide you in the right direction, but DAX is all about how the data model is structured.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 20 May 2021, 12:40Delegate Laurence said...
RE: Fixed Last 4 weeks This year and last year
Hi Jens,
Thank you for your answer - I'll test it out, in the meantime, I did come up with another way around this
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)))
& then
Bookings This Year Last 4 Week Total = CALCULATE([Bookings This Year Last 4 Week By Day],ALL(Dates))
This second measure gives me what I need which is the last 4 weeks' bookings plotted as a straight line on a date axis. I've then got the same measures for Prior years, with which to get a % growth figure from.
Is there any way of making my 2 measures into one? I tried putting ALL into the first measure, but it didn't seem to work?
Thanks
Laurence
Thu 20 May 2021, 12:56Trainer Jens said...
RE: Fixed Last 4 weeks This year and last year
Hi Laurence,
Try:
Bookings This Year Last 4 Week By Day =
CALCULATE([Bookings This Year],
FILTER(All(Dates),Dates[Date]>=(today()-1)-(4*7)),FILTER(All(Dates),Dates[Date]<=(today()-1)))
I have not tested it, but my logic tells me that this should do it.
Otherwise you can store the first result inside a variable. Something like this:
Bookings This Year Last 4 Week Total =
VAR Last4WeekByDay =
CALCULATE([Bookings This Year],
FILTER(Dates,Dates[Date]>=(today()-1)-(4*7)),FILTER(Dates,Dates[Date]<=(today()-1)))
RETURN
CALCULATE([Bookings This Year Last 4 Week By Day],ALL(Dates))
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 20 May 2021, 13:17Delegate Laurence said...
RE: Fixed Last 4 weeks This year and last year
Thanks very much, Jens
Your formula works, however the VAR one didn't
Here's what I put in - any ideas on why this might be wrong. This is the first VAR formula I've tried
Bookings This Year Last 4 Week Total VAR =
VAR Last4WeekByDay =
CALCULATE([Bookings This Year],
FILTER(Dates,Dates[Date]>=(today()-1)-(4*7)),FILTER(Dates,Dates[Date]<=(today()-1)))
RETURN
CALCULATE(Last4WeekByDay,ALL(Dates))
When I put it in a table, it returns the same as the below formula
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)))
That being the number of bookings done for each day
Thu 20 May 2021, 13:25Trainer Jens said...
RE: Fixed Last 4 weeks This year and last year
Hi Laurence,
Sorry you are absolutely right.
It was going too fast.
Yes of course you will need to reference the name of the variable in the Return (here Last4WeekByDay)
RETURN
CALCULATE(Last4WeekByDay,ALL(Dates))
Well done Laurence.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 20 May 2021, 13:32Delegate Laurence said...
RE: Fixed Last 4 weeks This year and last year
Thanks Jens, but unfortunately i did reference the variable and it's still not working?
This formula doesn't sum up the total of the 4 weeks, it just gives it by day?
Bookings This Year Last 4 Week Total VAR =
VAR Last4WeekByDay =
CALCULATE([Bookings This Year],
FILTER(Dates,Dates[Date]>=(today()-1)-(4*7)),FILTER(Dates,Dates[Date]<=(today()-1)))
RETURN
CALCULATE(Last4WeekByDay,ALL(Dates))
Thu 20 May 2021, 13:43Trainer Jens said...
RE: Fixed Last 4 weeks This year and last year
Hi Laurence,
Can I please ask you to take a screenshot of the data model and a screenshot of the page and send it to info@stl-training.co.uk
I need to understand your data model to be able to understand what is going on.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Data Cleaning and TransformationEnsure your data is clean and well-structured before importing it into Power BI. Use Power Query Editor to clean and transform your data. This tool allows you to perform various operations like filtering, merging, and reshaping data, ensuring it's in the right format for analysis. |
