Custom Date Slicer - Due within 1 week, Due within 1 month, Due within 3 months

[tp2017]

I have been running in circles trying to locate a solution to create a slicer where I can have a slicer that in which the user can select the following:

Due within 1 week
Due within 1 month

Due within 3 months

My result set is a SQL Server view and it only returns records where there is a due date that is either today or in the future. Within the result set is the due date (CDDate) and the number of days from today to the due date (CDDays). So I can easily see the SQL where clause I would create to group these different slicer selections > where [CDDays] <= 7, where [CDDays] <= 30 or where [CDDays] <= 90. However, I cannot find any solution as to how to do this in Power BI. A Lot of solutions have calendar tables that contain dates, but I don't see how these solutions would consistently update such a table when one is looking to the future, not the past. I'm completely confused.

[Greg_Deckler]

@tp2017 Create a disconnected table using an Enter Data query with your 3 criteria. Then you can create a Complex Selector measure like the following:

Selection Measure = 
  VAR __Today = TODAY()
  VAR __DueDate = MAX('Table'[CDDate])
  VAR __Days = ( __DueDate - __Today ) * 1.
  VAR __Criteria = MAX( 'Slicer Table', [Value] )
  VAR __Result = 
    SWITCH( __Criteria
      "Due within 1 week", IF( __Days <= 7, 1, 0 ),
      "Due within 1 month", IF( __Days <= 30, 1, 0 ),
      "Due within 3 months", IF( __Days <= 90, 1, 0 )
    )
RETURN
  __Result
     

Then just filter your visual based upon Select Measure = 1.

[tp2017]

Thanks, I was able to locate a solution and while yours is OK, it doesn't quite provide the detail most would need to be able to use it. Here's what I did, step by step:

In the "Model view" I created a new table called "SpecialDates"

As my slicer needs to look to dates that are out in the future and not the past, so I also created a "Dates" table with a single "Date" column and popluated it with all dates from now through the next ten years, also in the "Model view"

DateTable = CALENDAR(DATE(2024,01,01),DATE(2034,12,31))

I set the relationships between the "SpecialDates" table, the "DateTable" and my view (vwUpcomingDue) that contains the data I need to report on as follows:

Note the filter direction "both" between "SpecialDates" and "DateTable"

This allowed me to create a slicer and filter on the "Period" field from the "SpecialDates" table and I can sort using the "Order" field.