cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Range of dates in a time slicer

Hi, I have a list of events with start date and end date.

 

I am using a time slicer to pick a range of dates. In timeslicer you can only put one value to display.

 

So if for example I have an event from June 4 to June 7 but I want to check from June 5 to June 7 the event won't get displayed because in the value of the slicer I was able to put only June 4.

 

Is there any other tool besides time slicer that can check ranges and not only one value?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Range of dates in a time slicer

Hi @paulaula81,

 

The Timeline slicer can be only put one column at the same time. To work around the issue, you can create another calendar table, then drag the date column from this calendar table to a slicer/timeline slicer visual. 

 

Create measures below: 

 

 

MinSelectedDate = 
VAR minselected =
    CALCULATE (
        Min ( 'calendar'[Date] )
        
    )
RETURN
    IF ( ISFILTERED ( 'calendar'[Date] ), minselected )

 

 

MaxSelectedDate = 
VAR maxselected =
    CALCULATE (
        MAX ( 'calendar'[Date] )
        
    )
RETURN
    IF ( ISFILTERED ( 'calendar'[Date] ), maxselected )

 

 

Measure = IF(MAX('Table1'[Start])>=[MinSelectedDate] && MAX('Table1'[End])<=[MaxSelectedDate] && MAX('Table1'[End])<>BLANK(),1,BLANK())

 

 q4.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Range of dates in a time slicer

Hi @paulaula81,

 

The Timeline slicer can be only put one column at the same time. To work around the issue, you can create another calendar table, then drag the date column from this calendar table to a slicer/timeline slicer visual. 

 

Create measures below: 

 

 

MinSelectedDate = 
VAR minselected =
    CALCULATE (
        Min ( 'calendar'[Date] )
        
    )
RETURN
    IF ( ISFILTERED ( 'calendar'[Date] ), minselected )

 

 

MaxSelectedDate = 
VAR maxselected =
    CALCULATE (
        MAX ( 'calendar'[Date] )
        
    )
RETURN
    IF ( ISFILTERED ( 'calendar'[Date] ), maxselected )

 

 

Measure = IF(MAX('Table1'[Start])>=[MinSelectedDate] && MAX('Table1'[End])<=[MaxSelectedDate] && MAX('Table1'[End])<>BLANK(),1,BLANK())

 

 q4.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Range of dates in a time slicer

Hi,

 

I'd suggest using the Query Editor to convert the two Date columns into a single Date column.  Then create a Calendar Table and build a relationship from the Date column of your source table to the Date column if your Calendar Table.  In your slicer visual, drag the Date from the Calendar Table.

 

To get more specific help, share some data and also show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors