cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
Super User III
Super User III

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

Hi @Anonymous 

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    

Anonymous
Not applicable

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. 

Anonymous
Not applicable

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
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors