topic Re: Display only first week of month based on week selection in DAX Commands and Tips

Display only first week of month based on week selection

joshua1990 — Tue, 08 Apr 2025 18:15:09 GMT

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?

Re: Display only first week of month based on week selection

lbendlin — Tue, 08 Apr 2025 19:01:21 GMT

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.

Re: Display only first week of month based on week selection

jewel_at — Tue, 08 Apr 2025 22:10:29 GMT

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

Re: Display only first week of month based on week selection

v-echaithra — Mon, 14 Apr 2025 11:53:05 GMT

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.

Re: Display only first week of month based on week selection

v-echaithra — Tue, 22 Apr 2025 09:17:30 GMT

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.

Re: Display only first week of month based on week selection

v-echaithra — Wed, 30 Apr 2025 06:18:43 GMT

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.