Filtering within a date range

[thmonte]

I have multiple tables with a Start Date column and and End Date Column and I would like to filter based on the Date Slicer falling within the range.  For example:

Type	Start Date	End Date
Sick	2/1/2017	3/1/2017

I want to be able to set the slicer for 2/15/2017 and have this record show up in my visuals but, the slicer is only able use one column as a filter.

How can I achieve this?

[v-ljerr-msft · Accepted solution]

Hi @thmonte,

---

---

I want to be able to set the slicer for 2/15/2017 and have this record show up in my visuals but, the slicer is only able use one column as a filter.

If I understand you correctly, you should be able to follow steps below to use a Date Slicer to filter your data on your visual. 

1. Add an individual Calendar table if you don't have one yet(make sure not create any relationship between this table and your original table).

Date = CALENDARAUTO()

2. Use the formula below to create a measure, and use it to apply a visual level filter(Count of Type is greater than 0) on your visual.

Count of Type =
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            ( Table1[Start Date] <= currentDate
                && Table1[End Date] >= currentDate )
        )
    )

Note: just replace 'Table1' with your real table name.

Regards

[Braxx99]

@v-ljerr-msft 
How to do the relatationship between 'Date' and 'Table1' when on right side you have [Start date] and [End date]?
Between which fields it should be done?


The measure [Count of Type] returs the count of rows in a selection. How it can filter visual ?

[thmonte]

Is this impossible to do if I am using DirectQuery?  I want to have my data refresh in real time once published.

[v-ljerr-msft]

Hi @thmonte,

Yes, it is. As we cannot add calculate tables in DirectQuery mode, you may need to add the Calender table on your source side instead. And the other steps are the same. 

Regards

[blopez11]

I think you would need to create a date table, relate it to your other tables based on date

Then create a filter based on date from your date table, and a filter on whatever table has the type

[thmonte]

@blopez11

How does that help me with filtering values within a date range?