cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moltra
Helper IV
Helper IV

Setup to same page format 4 current week, last week, 2 weeks ago, 3 week only change 1 current date

I have created a report that shows pages for the current weeks data, last weeks data, 2 weeks ago data, 3 weeks ago data, 4 weeks ago data.

 

The Page format is the same for the all the above week ranges, it shows 1 week of data so that upper management can review the results over a 5 week period.  

 

Currently, I manually select the Week Start Date for each week period page.  I am trying to figure out how to have it update the dates automatically, or how to setup so if I change the current week date, the other periods change as well.

 

What would be great would be to some how take the current week start date (monday) and subtract 7 from it for the prior week's page start date.

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@moltra , You can have a column like this

Week Type = Switch( True(),
[start week]<=Today() && [end date]>=Today(),"This Week" ,

[start week]<=Today() -7 && [end date]>=Today()-7,"Last Week" ,
[Week Name]
)

Sort this on the week start date. You can refer the link below to create a week start date

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

 

You can use this to filter this week last week.

Also refer to these blogs, How to deal with this week last week , rolling weeks

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

MFelix
Super User III
Super User III

Hi @moltra ,

 

You can do some things, place a slicer with the week start on all of the pages and then sinc those slicers so when you select on one of them you select on all the pages.

 

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers#sync-and-use-slicer...

 

Other option and if you have a calendar is to add the page Filter and chose the last n days so for each page it will update automatically.

 

You can also add a custom column to your calendar table that counts the number of weeks so on your report filter you filter by that value.

 

The column you need to add should be similar to and then choose on the filter the 1, 2, 3, 5:

Column = IF(WEEKNUM('Calendar'[Date]) >= WEEKNUM(TODAY())-5 && WEEKNUM('Calendar'[Date]) <= WEEKNUM(TODAY()) , WEEKNUM(TODAY()) - WEEKnum('Calendar'[Date]))

 

Be aware that this may need some changes especcialy because of year end changes and if you want to have the weekstart instead of current day.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



The sync slicers might work, I am using filters right now, but I could use slicers and then hide them from the end users.  I think I would have to create a with colums for the current week, last week... so that it would be easier to sync the slicers to the correct weeks. 

 

I do not understand what you are saying about the custom column.  

 

Thanks for the help

MFelix
Super User III
Super User III

If you create a custom column that shows value if it's within the last 5 weeks from today and then you filter your pages based on that column you won't need to filter out anything because automatically will be updated.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors