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

Count occurrences in a time interval

Hi everyone,

Have been struggeling with this for a while so I have to ask if someone can help me. Been going through many forum threads but I can't get anything to work.

 

I need to count the occurences of a phone number in my data set 14 days back and 14 days forwards. This is because we operate with an assumption that we have solved our customers problem the first time of contact (first call resolution) if the telephone number does not call within a two week interval (thus i need to check 14 days back and forwards).

 

By now I have this calculated column:

 

Count = 
VAR TwoWeeksFromDate = DATEADD(Table[Date];14;DAY)
VAR TwoWeeksbefore =
DATEADD(Table[Date];-14;DAY)
RETURN
CALCULATE (
COUNTROWS (Table);
FILTER (
ALLEXCEPT (Table; Table[PhoneNumber]);
Table[Date] <= TwoWeeksFromDate
&& Table[Date] >= TwoWeeksbefore))

But it does just work for some of my rows, other rows just show nothing! If I change TwoWeeksFromDate to CurrentDate it works, but then it will not count correctly.

 

Does anyone have any tips or something that can help me out? 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Ross73312 Super Contributor
Super Contributor

Re: Count occurrences in a time interval

Try this and let me know if it works:

Count = 
VAR TwoWeeksafter = [Date] + 14
VAR TwoWeeksbefore = [Date] - 14
VAR PHtoCheck = [PhoneNumber]
RETURN
CALCULATE (
	COUNTROWS (Table);
	ALL (Table);
	Table[PhoneNumber] = PHtoCheck;
	Table[Date] <= TwoWeeksFromDate;
	Table[Date] >= TwoWeeksbefore;	
)

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Count occurrences in a time interval

Hi @_Kristina_

Could you show me how your dataset look like?

For example, columns "date","phone number", what you need is to calculate the rows of each number which doesn't exsit within a two week interval for twice times.

I wonder whether my understanding of your dataset and calculating rule are right, so I would like to hear back from you.

 

Best Regards

Maggie

Ross73312 Super Contributor
Super Contributor

Re: Count occurrences in a time interval

Try this and let me know if it works:

Count = 
VAR TwoWeeksafter = [Date] + 14
VAR TwoWeeksbefore = [Date] - 14
VAR PHtoCheck = [PhoneNumber]
RETURN
CALCULATE (
	COUNTROWS (Table);
	ALL (Table);
	Table[PhoneNumber] = PHtoCheck;
	Table[Date] <= TwoWeeksFromDate;
	Table[Date] >= TwoWeeksbefore;	
)

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

_Kristina_ Regular Visitor
Regular Visitor

Re: Count occurrences in a time interval

Thank you @Ross73312!!

 

This worked out perfectly. Been struggeling for so long, thanks a lot.

_Kristina_ Regular Visitor
Regular Visitor

Re: Count occurrences in a time interval

Thanks, Maggie @v-juanli-msft. @Ross73312 provided a nice solution, but thanks for wanting to help 🙂

Highlighted
LRiley Frequent Visitor
Frequent Visitor

Re: Count occurrences in a time interval

Hi Ross,
Maybe you can help I'm looking to use the same technique as above however using two tables the first table will have phone number and date then I need to use the same logic but getting the number of occurrences from the second table

Thanks in advance

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors