Use week-slicer to filter visual on the according month

[IvBa]

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

[mark_endicott · Accepted solution]

@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. 

[mark_endicott]

@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!

[Anonymous · Accepted solution]

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.

[mark_endicott · Accepted solution]

@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.