Using overlapping date periods in a slicer

[hanrft]

I am working with dates that belong to multiple 'star ratings' periods. 

These are the current upcoming periods, they follow the same logic ongoing: 

Mar25 QTR = 1 Jun 23 to 31 May 24

Jun25 QTR = 1 Sep 23 to 31 Aug 24

Sep QTR = 1 Dec 23 to 30 Nov 24

Dec QTR = 1 Mar 24 to 29 Feb 25

I would like to be able to create a slicer so that if someone selected 'Mar25 QTR' they would be able to see all dates applicable to that period, however I am stuck in that I can only assign one period to each date, however each date applies to 4 periods. Currently my work around is having a bookmark for each period, however that also means that I have to keep adding new bookmarks as needed.

Currently I have a custom column to identify the first quarter they belong to as below:

StarRatingsQtr =

SWITCH(

    TRUE(),

    'Date'[Month] >= 3 && 'Date'[Month] <= 5, "Mar QTR" & " " & 'Date'[Year]+1,

    'Date'[Month] >= 6 && 'Date'[Month] <= 8, "Jun QTR" & " " & 'Date'[Year]+1,

    'Date'[Month] >= 9 && 'Date'[Month] <= 11, "Sep QTR" & " " & 'Date'[Year]+1,

    'Date'[Month] = 1, "Dec QTR" & " " & 'Date'[Year],

    'Date'[Month] = 2, "Dec QTR" & " " & 'Date'[Year],

    'Date'[Month] = 12, "Dec QTR"& " " & 'Date'[Year]+1)

I have been able to create a column where I can list more than one period in for each date, however it is very manual and I still can't use it to filter effectively.  

TestStarRatings2 =

VAR _QTR124 = IF('Date'[Date] >= DATE(2023,06,01) && 'Date'[Date] <= DATE(2024,05,31), "Mar QTR 2024")

VAR _QTR224 = IF('Date'[Date] >= DATE(2023,09,01) && 'Date'[Date] <= DATE(2024,08,31), "Jun QTR 2024")

RETURN

    _QTR124 & ", " & _QTR224

Is there a way to have a column or measure that can be filtered as I need??

[Anonymous]

Thanks for the reply from govind_021 , please allow me to add some more information:
Hi  @hanrft ,

You can try to create a slicer table with “Mar25 QTR” “Jun25 QTR”, create a measure with the Switch() function to customize the rule, place the Filter with 1 or 0.

Here are the steps you can follow：

1. Home – Enter data – Create a slicer table.

2. Create measure.

Flag =
var _select=SELECTEDVALUE('Slicer_Table'[Group_Slicer])
RETURN
SWITCH(
    TRUE(),
_select="Mar25 QTR" && MAX('Table'[Date])>=DATE(2023,6,1) && MAX('Table'[Date])<=DATE(2024,5,31),1,
_select="Jun25 QTR" && MAX('Table'[Date])>=DATE(2023,9,1) && MAX('Table'[Date])<=DATE(2024,8,31),1,
_select="Sep QTR" && MAX('Table'[Date])>=DATE(2023,12,1) && MAX('Table'[Date])<=DATE(2024,11,30),1,
_select="Dec QTR" && MAX('Table'[Date])>=DATE(2024,3,1) && MAX('Table'[Date])<=DATE(2025,2,29),1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

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

[govind_021 · Accepted solution]

Hi @hanrft 
I think you need to create a table where you should include one date in different time periods.
Please check out this video where there is similar kind of requiremnet 
https://youtu.be/hDopw1mPlrU?si=rXTDCP9zGBvzjBW3

[hanrft]

Thank you! This is exactly what I am looking for in terms of filtering. 

Any ideas on how to set up the VAR logic so that I don't need to manually identify which period each date falls into?

If I do as suggested in video this is my formula:

StarRatingsPeriod =

VAR _Mar24QTR = ADDCOLUMNS(

    CALCULATETABLE(

    'Date',

     DATESBETWEEN('Date'[Date],DATE(2023,06,01),DATE(2024,05,31)))

    , "Star Ratings Period", "Mar24 QTR")

VAR _Jun24QTR = ADDCOLUMNS(

    CALCULATETABLE(

    'Date',

     DATESBETWEEN('Date'[Date],DATE(2023,09,01),DATE(2024,08,31)))

    , "Star Ratings Period", "Jun24 QTR")

RETURN

    UNION(_Mar24QTR,_Jun24QTR)