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!

jitendra89doon
New Member

FInd Difference Between Current and previous ticket

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

Email

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

Email

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

Email

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

Email

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

"https://exceleratorbi.com.au/use-power-query-compare-database-records/#comment-29853-Current%20vs%20... "

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

0 REPLIES 0

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.