Dynamic Bookmark

[RKK]

Hi All, 

I have created two book marks

1. Last week 

2. Select week

By default, the "Last week" bookmark is selected, but users can choose the "Select week" bookmark to view data for other weeks.

For the "Last week" bookmark, I’ve created a calculated column to display the details of the previous week. However, it seems that when I move into a new week, the "Last week" bookmark still displays data for the week when the bookmark was first created, rather than updating dynamically. I need the "Last week" bookmark to automatically adjust and reflect the most recent complete week’s data.

I can’t use the Relative Date filter, as my week starts on Thursday and ends on Wednesday. For instance, when it’s the current week (week of 19th - 25th Dec), the "Last week" bookmark should show data for 12th - 18th Dec. When we transition to 26th Dec, I want the bookmark to dynamically update and show data for the week of 19th - 25th Dec.

Please help me with the workaround, and appreciated your assistance. 

[Anonymous]

Hi @RKK ,
For your problem, it may be a problem with the calculation column, we try to create a new calculation column to test hope it will help you!

IsLastWeek = 
VAR TodayDate =TODAY()
VAR CurrentWeekStart = TodayDate - MOD(WEEKDAY(TodayDate, 2) + 3, 7)
VAR LastWeekStart = CurrentWeekStart - 7
VAR LastWeekEnd = CurrentWeekStart - 1
RETURN 
IF('Table'[Date] >= LastWeekStart && 'Table'[Date] <= LastWeekEnd, 1, 0)

We hereby change the date to December 26th can fulfill your needs, hope it will be helpful to you!

Check out my pbix file if you still have questions, hope it helps!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[RKK]

Hi @Anonymous 

Thank you for your response. I followed the steps you mentioned, but when I open the report for this week, I notice that the data from both the 19th of December and the 12th of December are being displayed. The calculated column (IsLastWeek) is filtering the data correctly, but I only want to see the data from the 19th of December. Ideally, this should automatically update to show the data for the last week when new data is entered in the following week.

The desired outcome is that when I click on the "Last Week" bookmark, it should display only the data for the previous week.

Thank you!

[ivana_tomekova]

I think it this case, you also need to create specific column, called e.g. WeekName, with Date values like you have 12/12/2024, etc. and with specific text value 'Last week' for previous week (no date). And for Last Week bookmark, you need to use this text value in slicer. 

If you would be using date values in slicer, PBI will always remember your last selection, which was in this case 12/12/2024 ... if you will change it to text field 'Last week', it would also stay selected by default, but in the background it would dynamically mean different timerange.... Example on month level:

You can eassily create it as conditional column: If IsLastWeek = 1 then 'Last Week' else WeekStart