Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Check if date slicer is consecutive

I have a date slicer made using date heirarchy (Year, Quarter, Month) from a date table, and I need to have a way to check if the selected values from the date slicer are consecutive.   If the date...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    That sounds.... Disgusting. Thankfully after some food I had an epiphany ğŸ˜… This will all be dependant on the values chosen in the Date Hierarchy slicer

     

    Test = 
    VAR MinDate = MIN(Calendar[Date])
    VAR DisMon = DISTINCTCOUNT([Date].[Month])
    VAR NewDate = DATE(YEAR(MinDate),MONTH(MinDate)+DisMon,DAY(MinDate))
    VAR MaxDate = Max(Calendar[date])+1
    Return NewDate=MaxDate
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

Please be more specific. consecutive on which level? Month only?  The lowest visible level?  What if the user opens the hierarchy to different levels?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Month I supposed, although it will primarily be used in a quarterly context. So for example, a person would be able to select Q4 2021 and Q1 2022, but not Q1 2021 and Q3 2021. Alternatively, you could select December 2021 - March 2022, butr not September 2021 - November 2021 and January 2022-July 2022.

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      4 years ago

      Yeah, this will be messy.  You will have to use ISINSCOPE() and then go from the lowest level to the highest. So if a month level is visible then apply the computation to the months, if the months are hidden but the quarters are visible then do it by the quarters etc.   Did I mention that this will be messy?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        That sounds.... Disgusting. Thankfully after some food I had an epiphany ğŸ˜… This will all be dependant on the values chosen in the Date Hierarchy slicer

         

        Test = 
        VAR MinDate = MIN(Calendar[Date])
        VAR DisMon = DISTINCTCOUNT([Date].[Month])
        VAR NewDate = DATE(YEAR(MinDate),MONTH(MinDate)+DisMon,DAY(MinDate))
        VAR MaxDate = Max(Calendar[date])+1
        Return NewDate=MaxDate