Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
I see no reason why the principle I have demonstrated in my video wont work for you. You simply have to apply a filter on type is visit before executing the rest of the pattern I have demonstrated using Power Query. Did you try that?
You may take a look at the post below and try using Table.Group.
https://community.powerbi.com/t5/Desktop/Custom-column-Index-or-Ranking-by-other-column/td-p/33864
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
Below measure I have used for the problem
Diff=var CurrentCloseDate var= CurrentTicketNumer var previousTicketCloseDate= calculate(Min(CloseDate),topn(1,CaseTable,CloseDate<CurrentCloseDate && CurrentTicketNumer=TicketNumber && type="Visit",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 && Type="Visit")
n return DateDiff(CurrentCloseDate var,previousTicketCloseDate,Day)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |