How to auto-refresh dates on a rolling 2 week period in my Power BI report

[kjohnanand]

Good morning, community.

I am currently working on a Power BI report connected to a lakehouse in Fabric. Our report tracks a rolling 2-week period using a date filter that references my calendar table. Since our backend dataset refreshes daily, I have to manually update the filter every day to show the latest 2-week period by default. I want to improve efficiency by automating this process so that the date filter updates to the latest rolling 2-week period automatically when our dataset refreshes in the morning. What is the simplest way to accomplish this?

One approach I tried was using bookmarks. I added an extra column to our date table using PySpark code to check whether a date falls within the latest rolling 2-week period available in our fact table.

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, max as spark_max, when, date_sub, lit

def add_rolling_2_weeks_column(spark, transactions_table, calendar_table):
   # Load the tables
   nyce_union_all_trans = spark.table(transactions_table)
   dates_2020_2025 = spark.table(calendar_table)

   # Calculate the max date from the transactions table
   max_date_row = nyce_union_all_trans.agg(spark_max(col("settdate"))).collect()

   # Extract the actual date value from the collected result
   max_date = max_date_row[0][0]

   # Calculate the date 14 days before the max date
   reference_date = date_sub(lit(max_date), 14)

   # Add the new column to the calendar table
   calendar_df = dates_2020_2025.withColumn(
       "Rolling2Weeks",
       when(
           (col("Date") >= reference_date) & (col("Date") <= lit(max_date)),
           "Rolling 2 Weeks"
       ).otherwise("All Time")
   )
   return calendar_df


spark = SparkSession.builder.appName("DateCategorization").getOrCreate()
dates_2020_2025 = add_rolling_2_weeks_column(spark, "nyce_union_all_trans", "dates_2020_2025")

With this setup, I created a hidden filter on the page that selects between “rolling 2 weeks” and “all time.” When “rolling 2 weeks” is selected, it should filter the dates to show only the latest 2 weeks. I then used bookmarks to toggle between a default view showing the rolling 2-week period and an all-time view that allows users to select dates beyond the latest 2 weeks. However, this method doesn’t update the date filter as expected. Instead of automatically showing the latest dates, it remains static, requiring manual updates.

Any help on this would be appreciated!

[v-jtian-msft]

Hello, lbendlin ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hi, @kjohnanand .I am glad to help you.

In addition to setting up a slicer, a common way to auto-refresh rolling 2-week dates in a Power BI report is to create a measure/calculate column for the filters data, and then display a measure in the filters area that matches the requirements.

Create a measure, label it with the last two weeks of data. (I've labeled it 1 here)

Subsequently set the data with M_lastTwoWeeks=1 in the filters area

Creating a calculate column is also an optional option

Here is my test code:
You need to write the appropriate dax code for your actual computing environment and data

C_lastTwoWeeks = 
VAR _today=TODAY()
VAR _twoweeks=_today-13
RETURN
IF('Sales'[Date] >= _twoweeks && 'Sales'[Date] <= _today,1,0)

M_lastTwoWeeks = 
VAR _date=MAX('Sales'[Date])
VAR _twoWeek = TODAY() -13
VAR _today =TODAY()
RETURN IF(_date <= _today && _date >= _twoWeek ,
1,0)

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[kjohnanand]

Hey, thank you for the reply! Would this method also adjust a date between slicer to show the max and min date of the latest 2 weeks every time the date refreshes?

For example, let's say my slicer defaulted to a min date of 10/15/24 and a max date of 10/29/24. Tomorrow, when the data refreshes, would that between slicer auto update to a min of 10/16/24 and a max of 10/30/24?

Thanks!

[v-jtian-msft · Accepted solution]

Hi,@kjohnanand .Thank you for your reply.

When I open this pbix file for the first time today it automatically scrolls through two weeks of data, as you can see from the previous reply screenshot it shows up to 10/28 the day before yesterday, but when I open it now, at the moment it shows up as 10/30
This is my current time zone time

Measure filters data only against the Today function, so as soon as the result of the Today function changes (the date moves backwards), when you open the report, the filter interval for the two weeks shown by the filter will be synchronised backwards.

You can see that the decision result for the calculate column will also change as the result of the Today function changes

Instead of using a slicer, the result of the measure/calculate column is used to display the data that satisfies the conditions.

Unfortunately for your request: "Does the default time option on the slicer automatically change based on updates based on the latest date"

This feature is not directly available in Power BI for the time being. The system cannot automatically update the option values on the slicer, you can only manually modify the filter range of the slicer, or use it
Relative Date style date slicer

You can try to achieve a similar effect using the same measure tag
For example, when I do not operate the slicer (simulate the state of the report just opened). table displays the last two weeks by default, and when I moved the date filter range on the slicer, the slicer worked fine.
Such an effect can be achieved
I hope the following issue can help you
You also need to ensure that the corresponding switch on the power BI Dektop is off.

URL:
Solved: Power BI Slicer - Between Dates don't get the late... - Microsoft Fabric Community
 I have given a few alternatives for you to choose from in this issue and I hope it helps.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

use the Filter Pane. It has advanced filtering including "Last X days".  Set X to 14.

[kjohnanand]

The issue with this is that I still want users to be able to select dates beyond the latest 2 week period. Setting a filter will prevent them from seeing historical data.

[lbendlin]

That is incorrect.  The filter is only the default setting.  Users can override it.

[kjohnanand]

Ahhh I see what you mean. Is there any way around using the filter pane? The rest of our filters are on the page itself and I don't want to confuse users by giving them a separate place to look just for the date filter. Ideally I would like to keep the between date slicer.

[lbendlin]

You may want to ask someone else. I am known to strongly dislike slicers - in my opinion they take up valuable screen real estate and should never be used.