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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Using COUNTROWS between a date range

Hi everyone

 

I have data with tickets that come in at various times, including multiple tickets on the same day. I am trying to ascertain repeat people who create tickets over the last 2 months.

I've written a column which gets pretty close, but not quite close enough, and I'm wondering how to get it over the line.

An example of my data is as follows (dates in UK format):

 

Created           Name               CountofNameinlast2months   

10/10/19         A.Nother          3                                                  

8/10/19           T.Lerone           1                                          

9/9/19             A.Nother          3

8/9/19             A.Nother          3

19/9/19           Mr.Tea             1

21/8/19           A.Nother          3

1/7/19             Mr.Tea             3

01/6/19           A.Nother          3

 

My expected output from above is the contents of the 'CountofNameinLast2months' column (assuming today is 23rd October)

As you can see I need the DAX to ignore A.Nother and Mr.Tea's tickets in June and July respectively.

 

I've been using COUNTROWS, because the created date will always be populated, but when I use DATESBETWEEN i get an error stating that the date column has multiple same dates, which is not supported.

 

Can anyone offer any assistance?

 

Thanks 🙂

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thanks for your help @Anonymous 

 

I got there myself in the end, by using the FILTER function inside the COUNTROWS, it has got me the deisred results.

 

Appreciate you spending the time.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @Anonymous 

 

You can create a column to filter out data older than 2 months. 

 

Filter = 
var monthsdiff = DATEDIFF('Table (2)'[Created], NOW(), MONTH)

return if(monthsdiff <=2, "Show" , "Hide")

Capture.PNG

 

Anonymous
Not applicable

Thanks for your help @Anonymous 

 

I got there myself in the end, by using the FILTER function inside the COUNTROWS, it has got me the deisred results.

 

Appreciate you spending the time.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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