Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

@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...

 

 

MFelix
Super User
Super User

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.