cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jitendra89doon Frequent Visitor
Frequent Visitor

fFInd 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

5 REPLIES 5
jitendra89doon Frequent Visitor
Frequent Visitor

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

jitendra89doon Frequent Visitor
Frequent Visitor

Re: fFInd Difference Between Current and previous ticket

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)

Community Support Team
Community Support Team

Re: fFInd Difference Between Current and previous ticket

@jitendra89doon,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jitendra89doon Frequent Visitor
Frequent Visitor

Re: fFInd Difference Between Current and previous ticket

Dear Sam,
 
This blog is irrelevant  to answer the problem, if power bi unable to deal with such complex problem then we have concluded, power bi is not usefull for handling large database. It's visualization tool for small amount of data.
 
 
Regards
Jitendra

Re: fFInd Difference Between Current and previous ticket

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?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors