Hi,
I want to add a column that tells me if the data is within the last 6 weeks. I want to look at the last date in a column and then indicate if the rows of data in the table are within 6 weeks of that date.
The date column is called "W/E Date". The new column will be "Last 6 Weeks". The values in this column will be "Yes" if within 6 weeks or "No" if not.
I am guessing that the solution will somehow take the max of the date in the "W/E Date" field and then calculate if the dates in the rows are within 6 weeks of the max date. I just don't know how to do it in Power Query editor.
Any help greatly received as always.
Bax
Hi Bax,
You can create variables in your query that you can reference. See file attached.
Mynda
Hi Mynda,
Thanks for the file. Is there any way I can pick up the last date in the "W/E Date" column instead of the current date in the TodaysDate step? The last W/E Date may not be the current date.
Thanks
Bax
Hi Bax,
Yes, and I've already used that technique to calculate the Date6WeeksAgo, so I'm going to let you figure out how to do it as practice. If you get stuck, show me what you tried, and I'll help you from there, but I reckon you can figure it out though so, me telling you how won't help you learn as much as you doing it yourself.
And this isn't me being lazy because it's taken me longer to write this than to give you the answer. I want to encourage you to try and figure these things out for yourself.
Mynda
Thanks Teacher, consider my knuckles rapped. I will figure it out and let you know how I did it 🙂
Hi,
Take a look at the attached. I have added my own data table which shows the Sunday week end dates between 07/01/24 and 25/02/24. I want to indicate the last 6 weeks from 25/02 so this would be the week ending 21/01/24 to 25/02/24 inclusive.
If you look at the table in column D this uses your query and as you can see it actually indicates 7 weeks from 14/01 to 25/02.
If you look at the table in column G I have modified your query so that it picks up the 6 weeks I am interested in. If you look at the query behind this "Query_Bax" you will see that I haven't used the "TodaysDate" step that is in your query. So I am still not sure why this is needed in yours.
Finally I have added a different query for the table in column J. The query "Query_Bax_Last6" uses a combination of the Duration.Days, Date.AddDays and List.Max to work out the actual week number based on the last date being week 1 and then previous weeks being incremental. I then filtered the list to show values <=6 to get my last 6 weeks. This query is quite useful as I can easily change it to pick up an number of weeks I want to show.
Thanks for your help as it put me on the track to finding a solution that suits my needs. Still wondering about the TodaysDate step. What am I missing here?
Thanks
Bax
Well done!
I wasn't sure if your dataset could contain future dates, or if it only contained dates up to the current period. If your dataset contained future dates, then you might want to check that you're not looking at dates in the future, in which case you want dates <= today's date.
= Table.AddColumn(#"Changed Type", "6 Weeks Ago", each [#"W/E Date"] >= #"Date6WeeksAgo" and [#"W/E Date"] <= #"TodaysDate")
This is why it's always best to provide a sample file so these questions that you don't think are relevant are answered by the data.
Mynda
Hi Mynda,
Understand now. In this case I always want to pick up the last date so I don't need to include the TodaysDate bit.
Thanks again for your help. Invaluable as always.
Cheers
Bax
