Skip to main content
cancel
Showing results for 
Search instead 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

Reply
tp2017
Regular Visitor

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

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.

2 REPLIES 2
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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"

tp2017_0-1719840497786.png

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:

tp2017_1-1719840755078.png


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.

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors