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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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")))))))
 
 
1 ACCEPTED SOLUTION
AlB
Super User
Super User

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 

SU18_powerbi_badge

View solution in original post

6 REPLIES 6
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

 

AlB
Super User
Super User

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 

SU18_powerbi_badge

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

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
Not applicable

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

Anonymous
Not applicable

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. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.