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

[Anonymous]

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")))))))

[AlB]

Hi @SimonSeez 

You'll have to explain exactly what you need, best with a clear example based on data and the expected result.

Try this in your card visual, a new measure that uses the one above:

MeasureDays TOT =
SUMX( DISTINCT( Table1[ContactRef] ), [MeasureDays] )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

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)

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[talbrechtsen]

Could someone explain the DaysInPeriod variable? Specifically the PeriodStart_ and PeriodEnd_? Is this just any date range?

[SimonSeez]

Hello @AlB 

Thank you for this solution, it worked great for me. I would like to use it in a card visual so I suppose I want to use a count? How can I modify the script to achieve this? 

Best Regards, 

Simon 

[Anonymous]

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. 

[Anonymous]

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.