cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors