cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JINEO Regular Visitor
Regular Visitor

Can you filter by NOT in the last 30, 60, 90, less than 90 days or NOT in a date range?

Hi Powerbiers, 

 

I have been playing with this one to no avail. Does anyone know a measure or formulised column or other that can do perform a NOT to a date range

 

Simply put I want to present a list of customer IDs who haven't had an activity type/s within a date range? 

 

Sample of source data https://1drv.ms/u/s!Ajd0Cd9p3E6O4GucG_PSZT1dOmk2?e=BN3QpX

 

I tried just using a date filter up to or before a date say -30 days from today 13/08/19 so setting 13/07/19 but then it will just list customers who HAVE activity in that date rather than those that have NOT.

 

I've tried something like this but of course not in 30 days needs to exclude customers with the activity type in the last 30 days or older, same with 60, 90 and more than 90: 

 

Activity Date = IF(NoEventCont[Event Date]>=(TODAY()-30),"In last 30",(IF(NoEventCont[Event Date]>=(TODAY()-60),"Not in last 30 days",(IF(NoEventCont[Event Date]>=(TODAY()-90),"Not In last 60",(IF(NoEventCont[Event Date]>=(TODAY()-120),"Not In last 90","More than 90 days")))))))
 
 
3 REPLIES 3
Highlighted
Super User
Super User

Re: Can you filter by NOT in the last 30, 60, 90, less than 90 days or NOT in a date range?

Hi @JINEO 

Try this:

1. Place Table1[ContactRef] in a table visual. Make sure you select 'Don't summarize'

2. Create this measure:

MeasureDays =
VAR PeriodStart_ = TODAY () - 30 // Example for the last 30 days. Modify as desired
VAR PeriodEnd_ = TODAY ()
VAR DaysInPeriod_ =
    GENERATESERIES ( PeriodStart_; PeriodEnd_ )
VAR DaysWithActivity_ =
    DISTINCT ( Table1[Event Date] )
RETURN
    IF ( COUNTROWS ( INTERSECT ( DaysInPeriod_; DaysWithActivity_ ) ) = 0; 1 )

3. Place the measure in the visual level filters of the table visual and select to show when measure value is 1.

 

You could create several measures for the different periods or if you want something more sophisticated, create a table with the start and end values for the period and select them with slicers (the measure code would in that case need to be updated)

 

Cheers    

JINEO Regular Visitor
Regular Visitor

Re: Can you filter by NOT in the last 30, 60, 90, less than 90 days or NOT in a date range?

Thanks @AlB , this seems to work. I get performance when joining to other tables but I can work around this. 

 

The thing I am struggling with is now creating a slicer so someone can tick whether they want 30, 60, 90 days. Measures can't be put in slicers so need to create a column or as you say a table but not sure what you mean by different start/end for the periods and how this would relate/affect the original measure.

 

Thanks for all your help. 

JINEO Regular Visitor
Regular Visitor

Re: Can you filter by NOT in the last 30, 60, 90, less than 90 days or NOT in a date range?

Hi @AlB , In addition, to providing a slicer I am having trouble with another aspect of this report. 

 

I am slicing by an activity type associated to the customer ID. e.g a "Call" with the NOT in the last 30 days works great (except for having slicers as mentioned in last comment). 

 

I need to also say that the customer has had another type of activity in a date range e.g an "email" between 1/1/19 and 1/3/19 and then want to apply the NOT with a Call in last 30 days. So I have another table with the same data joined. I have a visual filtering out customers with Email but now the measure is in play it isn't taking the other visual/slicer filters into account. Hope this makes sense. report.png

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)