Week range as matrix header to be filtered by the date slicer

[Eva_Zhu]

Hi guys, I'm seeking for help about the date filter problems in power bi and thanks in advance for any helps.
I'm using the WeekStart - WeekEnd as WeekRange in my date table as the column header of a matrix. Meanwhile, I have a date range slicer for the content in matrix. 

What I'm trying to get is to make the week range can be also change as the date slicer, for example,  the first column should be 1 Nov - 1 Nov and the last column header to be 30 Nov - 30 Nov as the date filter is Nov.

Is it possible?

[Bibiano_Geraldo · Accepted solution]

Hi @Eva_Zhu ,

First create a new calculated column in your calendar using this DAX:

WeekNo = 
DATEDIFF(
    STARTOFMONTH('Calendar'[Date]),
'Calendar'[Date],
    WEEK
) + 1

No create another calculated column using this DAX:

WeekRange = 
VAR vWeek = 'Calendar'[WeekNo]
VAR vYear = 'Calendar'[Date].[Year]
VAR vMonth = 'Calendar'[Date].[Month]
VAR firstDay = 
CALCULATE(
    FORMAT(
        FIRSTDATE('Calendar'[Date]),
        "DD Mmm"
    ),
    FILTER(
        'Calendar',
        'Calendar'[WeekNo] = vWeek &&
        'Calendar'[Date].[Year] = vYear &&
        'Calendar'[Date].[Month] = vMonth
        
    )
)

VAR lastDay = 
CALCULATE(
    FORMAT(
        LASTDATE('Calendar'[Date]),
        "DD Mmm"
    ),
    FILTER(
        'Calendar',
        'Calendar'[WeekNo] = vWeek &&
        'Calendar'[Date].[Year] = vYear &&
        'Calendar'[Date].[Month] = vMonth
    )
)

RETURN
firstDay & " - " & lastDay

Now drop the WeekRange column to your columns field in Matrix, and use date column from the calendar in your slicer.

Try to filter the range of dates and let me know if it works.

[Eva_Zhu]

Thanks so much for your solution! Although it may not work on my original plan which was to filter with the exact date range, but based on your solution I changed my date slicer to "by month" and it worked very well~ 

[Bibiano_Geraldo]

I'm happy to hear that you found the solution.

[123abc]

I think you’re trying to dynamically adjust the week range headers in your Power BI matrix based on a date slicer. This is definitely possible with a bit of DAX magic. Here’s a step-by-step guide to help you achieve this:

1. Create a Date Table: Ensure you have a date table in your model with columns for Date, WeekStart, and WeekEnd.

2. Create a WeekRange Column: Add a calculated column in your date table to combine WeekStart and WeekEnd into a single string.

   WeekRange = FORMAT([WeekStart], "dd MMM") & " - " & FORMAT([WeekEnd], "dd MMM")

3. Create a Measure for Dynamic WeekRange: This measure will adjust the week range based on the selected date range in the slicer.

   DynamicWeekRange = 
   VAR MinDate = MIN('Date'[Date])
   VAR MaxDate = MAX('Date'[Date])
   RETURN
   IF(
       [WeekStart] >= MinDate && [WeekEnd] <= MaxDate,
       [WeekRange],
       BLANK()
   )

4. Use the Measure in Your Matrix: Place the DynamicWeekRange measure in the column headers of your matrix.

5. Adjust the Matrix Settings: Ensure your matrix is set to show items with no data, so the headers adjust dynamically.

This setup should allow your week range headers to update based on the date slicer selection. If you need further customization or run into any issues, feel free to ask! 😊

Good luck with your Power BI report!

[Eva_Zhu]

Hi thanks so much for your help🙌.

However as I tried your method, I still run in to some problems.
I do have a date table and the fields are like below, with WeekStarting, WeekEnding and WeekRange:

However as I tried to create the dynamic week range it poped error

Also I'm not sure how to use a measue as matrixs' row header as I'm not quite familiar with this tool  ;(

Could you please help? Thanks so much!