How to measure recurring Ticket ID in the span of three months

[juliopatra]

Is there a way to measure in POWER BI the recurring fault ticket say for the last 3 months.

Scenario is the same customer (Circuit ID) has an outage on July 10, 2024, then the following Month (August) it again has an outage. Is there a way in POWER BI for me to know that this CIRCUIT ID: 456111 has reocurred.

Example data:

1st: outage Month of July

Company name : ABC Enterprises

Circuit ID : 456111

Restoration Ticket date: July 10, 2024

Reason for outage: Fiber cut

2nd: outage Month of August

Company name : ABC Enterprises

Circuit ID : 456111

Restoration Ticket date: August 5, 2024

Reason for outage: Fiber cut

Thank in advance

[v-zhangtin-msft · Accepted solution]

Hi, @juliopatra 

Assuming your data is in this format.

Two tables convert data in Power Query: Transpose-Use First Row as Headers-Rename first column-Append.

In the Power BI Desktop:

Measure = CALCULATE(COUNT(Append1[Circuit ID]),ALLEXCEPT(Append1,Append1[Restoration Ticket date]))

Count for different dates 456111, when it is greater than or equal to 2, it is a reoccurrence.

Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[juliopatra]

Thank you so Much, It's another way to get things done. I really appreciate all the help!

[juliopatra]

Thank you so Much for the response, I will be trying it out and let you know of the results.

Thank you!!!

[SaiTejaTalasila]

Hi @juliopatra ,

In transform data sort date/time column desc .You can create one calculated column and mark the occurrence of circuit Id as 1,2,3.. .You can use this data to identify the I'd which are repeating.

I hope it will be helpful.

Thanks,

Sai Teja 

[juliopatra]

@SaiTejaTalasila Thank you for the idea, i will also try it and let you know of the result.

Thank you!!

[SamInogic]

Hi,

You can achieve this functionality using measures in Power BI. Follow these steps to implement the solution:

1. Prepare Your Data
   Make sure your dataset has columns like Circuit ID, Company Name, Restoration Ticket Date, and Reason for Outage.
2. Create Month and Year Columns
   Add calculated columns to extract the month and year from the Restoration Ticket Date.
• Month:

Month = MONTH('SampleTable01'[Restoration Ticket Date])

• Year:

Year = YEAR('SampleTable01' [Restoration Ticket Date])

3. Create a Measure for Recurring Faults
Write a measure to calculate the number of times the same Circuit ID had an outage in the past three months:

Recurring Fault Count =

VAR CurrentCircuit = SELECTEDVALUE('SampleTable01' [Circuit ID])

VAR CurrentDate = MAX('SampleTable01' [Restoration Ticket Date])

RETURN

    CALCULATE(

        COUNTROWS('SampleTable01'),

        FILTER(

            'SampleTable01',

            'SampleTable01' [Circuit ID] = CurrentCircuit &&

            'SampleTable01' [Restoration Ticket Date] <= CurrentDate &&

            'SampleTable01' [Restoration Ticket Date] > EDATE(CurrentDate, -3)

        )

    )

4. Create a Flag for Recurring Outages
Add another measure to flag whether an outage is recurring:

Is Recurring =

IF(

    [Recurring Fault Count] > 1,

    "Yes",

    "No"

)

5. Visualize the Data

Add a table or chart in Power BI showing columns like Company Name, Circuit ID, Restoration Ticket Date, Reason for Outage, and the measures Recurring Fault Count and Is Recurring.

Hope this helps.

Thanks!