Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Customer | Activity | Start Date | End Date | Last Activity Date | Last Contact Date |
ACME Manufacturing | Startup | 6/10/2019 | 7/15/2019 | 6/27/2019 | 6/29/2019 |
Ace Corporation | Processing | 6/15/2019 | 6/30/2019 | 6/30/2019 | 7/1/2019 |
ABC Enterprises | Demolition | 6/12/2019 | 6/19/2019 | 6/19/2019 | 7/3/2019 |
Thank you!
Solved! Go to Solution.
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 () )
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.
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 () )
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.
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |