cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dapperscavenger
Resolver I
Resolver I

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:

 

Capture.PNG

 

 

 

 

 

 

 

 

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

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Solution Sage
Solution Sage

@dapperscavenger  & @mahoneypat 

I was going to suggest not using the Week Number, but consider changing to the "Start of Week" date.

DarylLynchBzy_0-1642616440381.png

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.




View solution in original post

3 REPLIES 3
Daryl-Lynch-Bzy
Solution Sage
Solution Sage

@dapperscavenger  & @mahoneypat 

I was going to suggest not using the Week Number, but consider changing to the "Start of Week" date.

DarylLynchBzy_0-1642616440381.png

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 😛

mahoneypat
Microsoft
Microsoft

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors