Display only first week of month based on week selection

[joshua1990]

Hi experts!

I have a report with a matrix visual and a slicer on top. The slicer contains the week number information.

Now, if I select a specific week I would like only to see the actual information for week 1 of the given month.

For instance

• If I select week 3, then I want only to see the week 1 from January
• If I select 14, then I want only to see first week of April
• etc

How is that possible?

[v-echaithra]

Hi @joshua1990 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[v-echaithra]

Hi @joshua1990 ,

Hi, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[v-echaithra]

Hi @joshua1990 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[jewel_at · Accepted solution]

I would approach it this way, assuming you have a date table connected to your fact table. 

1. Add a column for Week of the Year and Week of the Month to you date or calendar table

m in power query:

  #"Added WeekNumOfYear" = Table.AddColumn(#"PreviousStep", "WeekNumOfYear", each Date.WeekOfYear([Date]), type text),
  #"Added WeekNumOfMonth" = Table.AddColumn(#"Added WeekNumOfYear", "WeekNumOfMonth",  each Date.WeekOfYear([Date]), type text),
#"Added WeekNumOfYearMonth" = Table.AddColumn( #"Added WeekNumOfMonth", "WeekNumOfYearMonth",  each [WeekNumOfYear] & "-" & [WeekNumOfMonth] )

2. Create a calculated table with Dax that would contain the mapping from Year's week number to the first week of the month. 

for example:

WeekTable2025 = 
ADDCOLUMNS(
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                'Date',
                YEAR('Date'[Date]) = 2025
            ),
            "WeekNum", WEEKNUM('Date'[Date], 2)  -- 2 = Monday as first day
        )
    ),
    "WeekNum-1", FORMAT([WeekNum], "0") & "-1"
)

3. Create relationship between the WeekTable2025 [WeekNum-1]  and your Date Table [WeekNumofYearMonth]

4. Add the WeekTable2025 [WeekNum] field to your slicer.

Hope that helps!

Jewel 

[lbendlin]

Hopefully you will be aware that this makes no business sense whatsoever.  "First Week of Month" is like nailing pudding to a wall.

Anyway.

1. from your selection identify the month

2. for that month identify the days that fall into the first week

3. Display the data for that date range.