cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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 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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
4 REPLIES 4
Super User

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
Not applicable

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.

Super User

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
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors