Number of repeated Tickets per month

[king2005r]

Hi

I want your help to get below requirment report :

1-Number of repeated Tickets ( more than 1 time for customer name) within resoulations date ( month ) and close root cause is repeated more than 1 time within same month

2- Monthly total count of repeated incidents percentage from total incident

Demo Data :

Ticketnumber	Service Name	Product Name	Resolution Date	Customer Name	Circuit Id	Root Cause
334657	Remot	Remot	9/8/2019 11:21	Test1	Test1	Power
334694	Remot	Remot	9/1/2019 9:21	Test1	Test1	Power
335155	Remot	Remot	8/2/2019 22:04	Test1	Test1	Power
335322	Remot	Remot	8/5/2019 22:12	Test1	Test1	Cute
336292	Remot	Remot	9/3/2019 19:34	Test2	Test2	Cute Cute
336307	Remot	Remot	9/1/2019 13:44	Test2	Test2	Power
336310	Remot	Remot	9/2/2019 6:20	Test2	Test2	power
336335	Remot	Remot	9/1/2019 14:25	Test2	Test2	Cute Cute
336351	Remot	Remot	9/3/2019 15:59	Test3	Test3	Cute Cute
336374	Remot	Remot	9/1/2019 20:51	Test2	Test2	Power

Report will be :

Customer Name	Month	Count of repeat	Repeated root cause
Test1	Sep	1	Power
Test2	Sep	2	Power

[amitchandak]

create as new column

last date of same issue in this month =
maxx(filter(table,table[Customer Name]=earlier(table[Customer Name]) && table[Root Cause]=earlier(table[Root Cause])
&& table[Resolution Date]<earlier(table[Resolution Date]) && month(table[Resolution Date]) =month(earlier(table[Resolution Date]))),table[Resolution Date])

repeat flag =
=
if(isblank(maxx(filter(table,table[Customer Name]=earlier(table[Customer Name]) && table[Root Cause]=earlier(table[Root Cause])
&& table[Resolution Date]<earlier(table[Resolution Date]) && month(table[Resolution Date]) =month(earlier(table[Resolution Date]))),table[Resolution Date])
),0,1)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[king2005r]

Okay it's work now but this give me date only of last issue not count of repeat time as example i want :

Customer Name	Month	Count of repeat	Repeated root cause
Test1	Sep	2	Power
Test2	Sep	2	Power

first tickts will count as 0, not repeat but next tickts on same month will be 1 repeat

@amitchandak 

[amitchandak]

So if you are getting a date and as per Logic, I assume you getting only in the same month(In new column) . You can put if not isblank then 1 else 0 .

Then sum it up in visual.

I think I suggested a flag too. Is it giving wrong answer using flag ?

[king2005r]

Yes Amitchandak, your solutions i think it can be work after this edit, how can i solve this problem please 

@amitchandak  thanks for your support

[amitchandak]

@king2005r 

Please share what you are getting after the formula and what is expected. I think I need more information to help you out.

Also share the formula you are using

[king2005r]

Hi  @amitchandak 

I am getting this now :

Customer Name	Month	Root cause	last date of the same issue in this month	repeat flag
Test1	Dec	Power	12/1/2019 11:50:29 AM	Sunday, December 31, 1899
Test1	Dec	Power	12/1/2019 11:50:29 AM	Sunday, December 31, 1899

Forumla :

1- 

last date of same issue in this month =

maxx(filter(Tickts,Tickts[Circuit Id]=EARLIER(Tickts[Circuit Id]) && Tickts[Root Cause]=earlier(Tickts[Root Cause])

&& Tickts[Resolution Date]<earlier(Tickts[Resolution Date]) && month(Tickts[Resolution Date])=month(earlier(Tickts[Resolution Date]))), Tickts[Resolution Date])

2-

repeat flag = if(isblank(maxx(filter(Tickts,Tickts[Customer Name]=earlier(Tickts[Customer Name]) && Tickts[Root Cause]=earlier(Tickts[Root Cause])

&& Tickts[Resolution Date]<earlier(Tickts[Resolution Date]) && month(Tickts[Resolution Date]) =month(earlier(Tickts[Resolution Date]))),Tickts[Resolution Date])

),0,1)

Needed Report :

Customer Name	Month	Root cause	Count of repeated time for Dec month with the same root cause	repeat flag
Test1	Dec	Power	3	Repeated if more than 1 time in the same month
Test2	Sep	Power	4	Repeated if more than 1 time in the same month

[amitchandak]

Based on the initial data you provided, I create this pbix : https://www.dropbox.com/s/ino5zbpymbnj81y/RepeatFlag.pbix?dl=0

Please check and let me know any changes in the logic you required.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[king2005r]

Hi @amitchandak 

I download file and apply forumla, but it give me 1 or 0 resoult, and some case it's not repated and give me 1 

What i need, is number of repeated incidents or complines customer has open within same month, example below : 

Customer Name	Month	Root cause	number of repeated times for Dec month with the same root cause
Test1	Dec	Power issue	3
Test2	Sep	Power issue	4

Thanks a lot for you help 

[amitchandak]

Is it possible for you to share data along with results after removing sensitive information?
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[king2005r]

Hi @amitchandak 

Below are the report after apply forumla, 

Ticketnumber	Product Name	Resolution Date	Customer Name	Circuit Id	Incident Category	Root Cause	Repeat Count Sheet1
334657	Water	9/8/2019 11:21:44 AM	Test1	QMDIA57	Circuit Down	Faulty Router	0
334694	Water	9/1/2019 9:21:22 AM	Test1	QMDIA57	Circuit Down	Circuit Under Migration	0
335155	Water	9/2/2019 10:04:27 PM	Test1	QMDIA57	Circuit Down	Faulty Modem	1
335322	Water	9/5/2019 10:12:58 PM	Test1	QMDIA57	Circuit Down	External Cabling Issue	1
336292	Water	9/3/2019 7:34:17 PM	Test1	QMDIA57	Circuit Down	Connectivity issue	1
336307	Water	9/1/2019 1:44:30 PM	Test2	BMDIA289	Circuit Down	Device Hanged	0
336310	Water	9/2/2019 6:20:10 AM	Test1	ABANET103	Circuit Down	Connectivity issue	1
336335	Water	9/1/2019 2:25:23 PM	Test1	ABANET103	Circuit Down	External Cabling Issue	0
336351	Water	9/3/2019 3:59:52 PM	Test1	BMDIA289	Circuit Down	Connectivity issue	1
336374	Water	9/1/2019 8:51:06 PM	Test1	ABANET103	Circuit Down	Connectivity issue	0
336375	Water	9/2/2019 3:08:28 PM	Test1	BMDIA289	Circuit Down	Customer Internal Issue	0
336414	Water	9/9/2019 5:14:12 AM	Test1	QAMDIA62	Circuit Down	External Cabling Issue	1
336559	Water	9/1/2019 8:46:10 PM	Test3	QAIPMW22	Circuit Down	Connectivity issue	0
336720	Water	9/2/2019 1:24:41 PM	Test1	MMADH68 IP9	Circuit Down	Faulty Modem	0
336761	Water	9/3/2019 2:59:58 PM	Test1	ABANET103	Circuit Down	Connectivity issue	1

[amitchandak]

This is a bit different from the last one. What is the expected output on this data.

[king2005r]

Hi @amitchandak 

it's the same but i add more data to be more clear

output data will be like this :

Customer Name	Circuit Id	Count of Circuit Id	Root Cause (Repeated more than 1 time)	Count of repeated)	Month
Test1	ABANET103	4	Connectivity issue	3	Sep
Test1	BMDIA289	3	No repeated root cause more than 1 time	No repeated root cause more than 1 time	No repeat
Test1	MMADH68 IP9	1	No repeated root cause more than 1 time	No repeated root cause more than 1 time	No repeat

[amitchandak · Accepted solution]

This logic seems to be based on  circuit ID, not the root cause. I changed it. Also, created an additional measure to show data only when sum >1

https://www.dropbox.com/s/ino5zbpymbnj81y/RepeatFlag.pbix?dl=0

[king2005r]

Great Job @amitchandak  this what i need, only i missing second filter for root cause

if root cause is repeated more than 1 time for the repeated Circute count as repeated only if root cause is repeated 

[king2005r]

Thanks a lot for your answe, when i apply this, i got below massage : 

Cannot convert value 'December' of type Text to type Date.