Use a month slicer to filter some visuals by month and others by past x months

[Anonymous]

Hi, I have one fact table and one date dimension table. There is a report with one page with many visuals and one slicer to select the month. Some visuals should be filtered to the selected month, others to the past 6 months, others to the past 12 months. How can I handle this without changing the requirement which was provided to me? My solution thus far has been to disconnect the date table and use measures for all values, so that I can provide the filter context inside each measure. Any more elegant solutions would be much appreciated. Thank you.

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

According to your description, I create this data:

Here are the steps you can follow：

1. Create date table.

Date = CALENDARAUTO()

2. Create calculated column.

Month = MONTH('Table'[date])

Month = MONTH('Date'[Date])

3. Create measure.

When slicer selects a date, a visual object displays the selected month:

Flag1 =
var _selectedmonth=SELECTEDVALUE('Date'[Month])
return
IF(
    MAX('Table'[Month])=_selectedmonth,1,0)

Put it in filters and set is = 1

When slicer selects date, a visual object displays the first 6 months of selection:

Flag2 =
var _selectedmonth=SELECTEDVALUE('Date'[Month])
return
IF(
    MAX('Table'[Month])=_selectedmonth-6,1,0)

Put it in filters and set is = 1

4. Result

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[amitchandak]

@Anonymous , if the relative date slicer is not working, then you have to use disconnected tables only.

Same what is also done here

https://www.youtube.com/watch?v=duMSovyosXE

or

https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-30/m-p/1284966#M561629