Check if date slicer is consecutive

[Anonymous]

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 dates selected are NOT consecutive, the page navigation button will not work. If the dates selected ARE consecutive then the user can continue onto the next page.

[lbendlin]

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]

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]

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 · Accepted solution]

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