Forum Discussion
Identify current week plus next 4 weeks in table
I have a query that returns for me a list of 365 days of dates, starting from 30 days back. It then identifies the ISO Week number, groups the dates by week, (with the date aggregating by min), and then identifies the current ISO week:
What I would like to do next is find a way to identify any week which is this week + 4. e.g. if it is week 3, I want to mark weeks 3, 4, 5, 6 & 7
It gets a bit complicated if it is currently week 51. Then I would want week 52, 1, 2, 3 & 4
The purpose is to later be able to have a filter where I can include or exclude these specific weeks from my calculations.
I also want to be able to exclude any week in the past.
Thank you
dapperscavenger & mahoneypat
I was going to suggest not using the Week Number, but consider changing to the "Start of Week" date.This option will ignore the resetting of Week Number. Instead you are looking current start of week plus 4 * 7 day in your conditional function.
3 Replies
dapperscavenger & mahoneypat
I was going to suggest not using the Week Number, but consider changing to the "Start of Week" date.This option will ignore the resetting of Week Number. Instead you are looking current start of week plus 4 * 7 day in your conditional function.
Yes this will do it. I think I was over complicting it in my head, when really it doesn't need anything more than a an if statement 😛
Please see these articles. One has a Weeks From Now column that you could use in your measures. The other has a Fiscal Week index you could also use (or adapt to be a fiscal weeks from now column).
No Sort Date Tables! – Hoosier BI
445 Calendar with 53-Week Years – Hoosier BI
Pat
