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

Filter across multiple date fields

I have a table with customer/activity entries and there are multiple dates associated with each.  I have a date slicer on my report and need the report to filter to those activities where any of the dates associated with the activity fall within the slicer range.  For example, if the slicer dates were 6/24/19 - 6/30/19, it would return the first two entries, but not the third, as none of that entries dates fall within the range.  Any suggestions how to accomplish this?

 

CustomerActivityStart DateEnd DateLast Activity DateLast Contact Date
ACME ManufacturingStartup6/10/20197/15/20196/27/20196/29/2019
Ace CorporationProcessing6/15/20196/30/20196/30/20197/1/2019
ABC EnterprisesDemolition6/12/20196/19/20196/19/20197/3/2019

 

Thank you!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter across multiple date fields

Hi @StaceyMcCain ,

 

1. To create a date table as below and add a slicer based on it.

 

date = CALENDAR(DATE(2019,06,01),DATE(2019,07,31))

2. To create a measure to get the excepted result as we need.

 

Measure = 
VAR st =
    MAX ( 'Table'[Start Date] )
VAR endd =
    MAX ( 'Table'[End Date] )
VAR lastd =
    MAX ( 'Table'[Last Activity Date] )
VAR lastcd =
    MAX ( 'Table'[Last Contact Date] )
VAR a =
    VALUES ( 'date'[Date] )
RETURN
    IF ( st IN a || endd IN a || lastd IN a || lastcd IN a, 1, BLANK () )

Capture.PNG

 

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

Re: Filter across multiple date fields

This is where I think a date dimension that includes week number data would be very useful.  You would just filter on where the WeekNo is the same. 

 

If you want to continue using this style of solution, you would edit it like this to get the previous 7 days of data from a single sliced date:

Measure = 
VAR st =
    MAX ( 'Table'[Start Date] )
VAR endd =
    MAX ( 'Table'[End Date] )
VAR lastd =
    MAX ( 'Table'[Last Activity Date] )
VAR lastcd =
    MAX ( 'Table'[Last Contact Date] )
VAR a =
    DATESBETWEEN('date'[Date], DATEADD('date'[Date],-6,DAY), MAX('date'[Date]) )
RETURN
    IF ( st IN a || endd IN a || lastd IN a || lastcd IN a, 1, BLANK () )

   Note that I'm only subtracting 6 days from the sliced date because it is inclusive. 

4 REPLIES 4
Super User
Super User

Re: Filter across multiple date fields

This is where a date dimension comes in handy.  Relate the date dimension to each of your dates, slice on the date dimension, and it should filter all of them.

Community Support Team
Community Support Team

Re: Filter across multiple date fields

Hi @StaceyMcCain ,

 

1. To create a date table as below and add a slicer based on it.

 

date = CALENDAR(DATE(2019,06,01),DATE(2019,07,31))

2. To create a measure to get the excepted result as we need.

 

Measure = 
VAR st =
    MAX ( 'Table'[Start Date] )
VAR endd =
    MAX ( 'Table'[End Date] )
VAR lastd =
    MAX ( 'Table'[Last Activity Date] )
VAR lastcd =
    MAX ( 'Table'[Last Contact Date] )
VAR a =
    VALUES ( 'date'[Date] )
RETURN
    IF ( st IN a || endd IN a || lastd IN a || lastcd IN a, 1, BLANK () )

Capture.PNG

 

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

Re: Filter across multiple date fields

Thank you, @v-frfei-msft , for this example... it's very helpful!  A twist on this... if, rather than a date range, I want to have my slicer be the 'week ending date' and have it filter for all activities where one of the four date fields fall in that week ending... how would I modify this?

Super User
Super User

Re: Filter across multiple date fields

This is where I think a date dimension that includes week number data would be very useful.  You would just filter on where the WeekNo is the same. 

 

If you want to continue using this style of solution, you would edit it like this to get the previous 7 days of data from a single sliced date:

Measure = 
VAR st =
    MAX ( 'Table'[Start Date] )
VAR endd =
    MAX ( 'Table'[End Date] )
VAR lastd =
    MAX ( 'Table'[Last Activity Date] )
VAR lastcd =
    MAX ( 'Table'[Last Contact Date] )
VAR a =
    DATESBETWEEN('date'[Date], DATEADD('date'[Date],-6,DAY), MAX('date'[Date]) )
RETURN
    IF ( st IN a || endd IN a || lastd IN a || lastcd IN a, 1, BLANK () )

   Note that I'm only subtracting 6 days from the sliced date because it is inclusive. 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 105 members 1,416 guests
Please welcome our newest community members: