Date Slicer Filtering Two Date Fields

[AaronJarboe]

Hello,

I have a "requests" dataset which includes both a StartDate and an EndDate column.  I need to create a slicer that will filter in any records where either the StartDate or EndDate falls within a date range as defined by the slicer.  Is this possible, or will I be required to have individual slicers for StartDate and EndDate?

[OwenAuger]

@AaronJarboe

Here is one way of handling it (pbix file here😞

1. Set up the data model with these tables/relationships:

2. Date is a standard date table 

3. Transaction Dates looks like this, providing the relationship between each "transaction" and StartDate and EndDate. Note the bidirectional relationship with Sales.

With this model, you can now apply filters on the Date table and the Sales table will be filtered to rows where either StartDate or EndDate meets the filter criteria.

Also, you could filter on Date Type if you wanted the Date filter to apply to StartDate or EndDate only.

Regards,

Owen

[v-frfei-msft]

Hi @AaronJarboe,

Does that make sense? If so, kindly mark the answer as solution to close the case.

Regards,
Frank

[v-frfei-msft]

Hi @AaronJarboe,

I made one sample for your reference.

1. Create the relationship between tables as below.

2. Create the measures.

End = CALCULATE(SUM(Table1[sales]),USERELATIONSHIP('CALENDAR'[Date],Table1[End date]))

St = CALCULATE(SUM(Table1[sales]))

For more details, please check the pbix as attached.

Regards,

Frank