Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

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 others find it more quickly.

View solution in original post

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. 

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

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 others find it more quickly.
Anonymous
Not applicable

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?

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. 

Cmcmahan
Resident Rockstar
Resident Rockstar

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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