How to modify the Slicer to show range of month based on month selected in another slicer

[Anonymous]

Hi 

My ask is how to modify the Slicer (Month name ) to show range of month based on month selected in another slicer.

For example:

If i select year to be 2023 & Month to be May in a slicer from data table, it have to modify the another month slicer in fact table to show Jan to May not just may.

Any help is welcomed.

[Anonymous]

Hi @Anonymous ,

You can try dax formula like below

StartDate = 
   MINX(
      FILTER(
         'DateTable',
         'DateTable'[Year] = SELECTEDVALUE('Year-Month Slicer'[Year1]) &&
         'DateTable'[MonthNum] = SELECTEDVALUE('Year-Month Slicer'[MonthNum1])
      ),
      'DateTable'[Date]
   )

MonthRange = 
CALCULATE(
    IF(
        COUNTROWS(
            FILTER(
                'DateTable',
                'DateTable'[Date] >= [StartDate] && 'DateTable'[Date] <= MAX('DateTable'[Date])
            )
        ) > 0,
        1,
        0
    )
)

Best Regards,
Adamk Kong

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

[Anonymous]

Hi @danextian 

Thanks a lot for quick response , 

Yes this method work well but small thing instead of having fixed x months, i have to show from jan to till the selected month.

How can this be done. i am thinking about creating a switch condition to put the value for x : for ex if april is selected x will be 4, may then x is 5 likewise for all 12 months. is there any simple alternative approach you can suggest.

[danextian]

You can just change the __START varible either by hardcoding the full date or by hardcoding just the month and day.

VAR __START = 
    DATE ( YEAR ( EOMONTH ), 1 , 1 )

[danextian · Accepted solution]

Hi @Anonymous ,

My suggestion involves establishing an additional Dates table without a relationship to the primary Dates table (disconnected). Subsequently, you can create a measure to assess whether the dates in the primary Dates table fall within a defined range. This measure will yield a blank result if the date is outside the specified range, enabling the slicer to be visually filtered to exclude blanks.

Here's a sample measure:

DateFilter = 
VAR __X_MONTHS = 5 //x months from EOMONTH
VAR __EOMONTH =
    EOMONTH ( MAX ( Dates2[Date] ), 0 ) //selected month's end of month
VAR __START =
    EDATE ( __EOMONTH, - __X_MONTHS ) + 1
RETURN
    CALCULATE (
        COUNTROWS ( Dates ),
        FILTER ( Dates, Dates[Date] >= __START && Dates[Date] <= __EOMONTH )
    )

Note: This approach does not work if the slicer is set to Between (bug!)

Please see attached pbix for your reference.