Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
Solved! Go to Solution.
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.
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")
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |