Forum Discussion

hanrft's avatar
hanrft
Regular Visitor
1 year ago
Solved

Using overlapping date periods in a slicer

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??
Need Help

3 Replies

    • hanrft's avatar
      hanrft
      Regular Visitor
      1 year ago

      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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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