Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team,
I have report where I need to calculate the difference between close date of same ticket number and its previous ticket number who’s type is visit. I have 22600000 record in my Table. I have use earlier function but doesn't working with large number of data.
Here is sample Data.
For e.g. My SQL look like this
Select DateDiff(Day,CurrentCloseDate,PreviousCloseDate) where Type=Visit
CASEID | CloseDate | Type | TicketID |
101 | 26-01-2018 | Visit | A1 |
101 | 27-01-2018 | ROT | A2 |
101 | 28-01-2018 | A3 | |
101 | 29-01-2018 | Visit | A4 |
101 | 30-01-2018 | ROT | A5 |
101 | 31-01-2018 | Visit | A 6 |
101 | 01-02-2018 | ROT | A7 |
102 | 01-02-2018 | Visit | A8 |
102 | 02-02-2018 | ROT | A9 |
102 | 03-02-2018 | ROT | A10 |
102 | 04-02-2018 | A11 |
My Result will look like
ID | CloseDate | Type | PreviousTicketID | Diff |
101 | 26-01-2018 | Visit |
|
|
101 | 27-01-2018 | ROT | A1 | 1 |
101 | 28-01-2018 | A1 | 2 | |
101 | 29-01-2018 | Visit | A1 | 2 |
101 | 30-01-2018 | ROT | A4 | 3 |
101 | 31-01-2018 | Visit | A4 | 2 |
101 | 01-02-2018 | ROT | A6 | 1 |
102 | 01-02-2018 | Visit |
|
|
102 | 02-02-2018 | ROT | A8 | 1 |
102 | 03-02-2018 | ROT | A8 | 2 |
102 | 04-02-2018 | A8 | 3 |
Please Don't suggest to use Earlier function, it’s doesn't work with large number of record. If I use Earlier function, when press enter after creating measure it always shows processing. I also use below measure and when I hit enter it always show's processing and to close power bi need to use task manager.
I have use Below Measures but doesn't work
Diff=var CurrentCloseDate var= CurrentTicketNumer var previousTicketCloseDate= calculate(Min(CloseDate),topn(1,CaseTable,CloseDate<CurrentCloseDate && CurrentTicketNumer=TicketNumber,asc)
return DateDiff(CurrentCloseDate var,previousTicketCloseDate,Day) ---Doesn't--
---Also Used----
Diff=var CurrentCloseDate var= CurrentTicketNumer var previousTicketCloseDate= calculate(Min(CloseDate),Filter(All(CaseTable),CloseDate<CurrentCloseDate && CurrentTicketNumer=TicketNumber)
n return DateDiff(CurrentCloseDate var,previousTicketCloseDate,Day)
I have been stuggling way to long to find the solution of the problem, I have found the blog
to the similar solution but it will only give difference bewteen current row and previous row but requirement is to calculate difference bewteen current row and it previous row who's type is visit
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |