topic Re: Use week-slicer to filter visual on the according month in DAX Commands and Tips

Use week-slicer to filter visual on the according month

IvBa — Wed, 18 Dec 2024 14:53:38 GMT

Hi all,

We want to create a report and faced a problem, that we will need to solve also for future reports.

In our current implementation, we use a slicer on one column of our date-table that filters the Calendarweek (Format YYYY.WW).

The values of a table-visual are filtered using this selected value and that works fine.

Now we have another visual, that we want to filter by the month that corresponds to the last day of our week. I.E. if we consider calendar week 48 in 2024 we get the 01.12.2024 as the last day of the week. So we want to filter our second visual to use all dates in december 2024.

Currently we are having problems creating a dynamic filter criterion that filters our second visual based on the corresponding month of the selection of our week slicer.

Does anyone have some suggestions how we can implement this in PowerBI?

Thanks in advance

Re: Use week-slicer to filter visual on the according month

mark_endicott — Wed, 18 Dec 2024 16:46:49 GMT

IvBa - You can use variables ( VAR ) to define a Range Start and End for your month, then use these in combination with REMOVEFILTERS( DateTable ) to change the filter context for the measures in your 2nd visual. Your measures will probably look something like this:

VAR _selected_week = MAX( 'Datetable'[week ending] ) VAR _range_start = EOMONTH( _selected_week, -1 ) +1 VAR _range_end = EOMONTH( _selected_week, 0 ) RETURN CALCULATE( [Measure], REMOVEFILTERS( 'Datetable' ), 'Datetable'[Date] >= _range_start && 'Datetable'[Date] <= _range_end )

If this sample DAX helps you, please mark it as the solution to help others with the same challenge.

Re: Use week-slicer to filter visual on the according month

Anonymous — Fri, 20 Dec 2024 02:07:52 GMT

Hi IvBa

Thanks to mark_endicott for the great reply.

Please allow me to add another solution:

1. Create a slicer with Calendarweek column

2. Create a measure with the following DAX:

FilteredValues = VAR LastDayOfWeek = MAX('Table'[Date]) VAR SelectedMonth = MONTH(LastDayOfWeek) RETURN CALCULATE( SUM('Table'[Amount]), FILTER( ALL('Table'), MONTH('Table'[Date]) = SelectedMonth ) )

Sample data:

Result:

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

Re: Use week-slicer to filter visual on the according month

mark_endicott — Mon, 23 Dec 2024 10:52:57 GMT

IvBa - did we resolve your issue? If we did, please select the appropriate solution, it helps with visibility for others and for SuperUsers to keep their status! Thanks!