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

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