Auto-Select Current Month and Week in Power BI Slicers
Inkey Solutions, May 2, 2025148 Views
Problem Statement
In a Power BI report with two pages —“Month” and “Week”— each page has its own slicer:
- The “Month” page has a Current Month slicer.
- The “Week” page has a Current Week slicer.
The objective is to automatically select the current month or current week in the slicers when a user navigates to the respective report page.
At present, the slicers either retain the last selected value or require users to manually select a period. The challenge is to make this behavior dynamic—so that the slicer defaults to the current month or week, ensuring the report remains up-to-date and more user-friendly.
Solution Approach
Step 1: Create Date Tables
Make sure your Power BI model includes a proper date table. This table should contain columns like:
- Date
- Month Name
- Month Number
- Month StartDate
- Week StartDate
- Week EndDate
- Week Number
Step 2: Create Calculated Columns for Current Month and Current Week
In Power BI Desktop, create two calculated columns to dynamically identify the current month and current week.
For Current Month:
Current Month =
IF (
‘Date Table'[Month Number] = MONTH( TODAY() ) && ‘Date Table'[Year] = YEAR( TODAY() ),
“Current Month”,
FORMAT(‘Date Table'[Month StartDate],”dd-MM-yyyy”)
)
For Current Week:
Current Week =
VAR CurrentDate = TODAY()
VAR WeekStart = ‘Date Table'[Week Start Date]
VAR WeekEnd = ‘Date Table'[Week End Date]
RETURN
IF (
CurrentDate >= WeekStart && CurrentDate <= WeekEnd,
“Current Week”,
FORMAT(WeekStart, “dd MMM yyyy”) & ” – ” & FORMAT(WeekEnd, “dd MMM yyyy”)
)
Step 3: Set Up the Slicers
Now that the calculated columns are ready, use them in your slicers.
Before doing that, go to the Data View:
- Sort Current Month by Month Number
- Sort Current Week by Week Number
This ensures the slicer values appear in the correct order.
When you use these calculated columns in your slicers, the selections will appear clearly labeled—for example, “Current Month” or “Current Week”—making it intuitive for users to identify the current period.
Once you select “Current Month” or “Current Week” in the slicer and publish the report to the Power BI Service, those values will remain selected by default when end-users open the report at Power BI Service. This ensures that viewers are always seeing the most relevant, up-to-date information without needing to make manual selection.
Conclusion
And that’s how you can configure slicers in Power BI to automatically select the current month or current week. This approach enhances your report’s interactivity, making it more dynamic and user-friendly for your audience.
Note:
Alternatively, you can also use the Preselected Slicer custom visual to achieve similar functionality if required.
