Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
juliopatra
New Member

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

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

 

1 ACCEPTED SOLUTION
v-zhangtin-msft
Community Support
Community Support

Hi, @juliopatra 

 

Assuming your data is in this format.

vzhangtinmsft_0-1733721506304.pngvzhangtinmsft_1-1733721520712.png

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

vzhangtinmsft_2-1733721679478.png

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.

vzhangtinmsft_3-1733721971788.png

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.

 

 

View solution in original post

6 REPLIES 6
v-zhangtin-msft
Community Support
Community Support

Hi, @juliopatra 

 

Assuming your data is in this format.

vzhangtinmsft_0-1733721506304.pngvzhangtinmsft_1-1733721520712.png

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

vzhangtinmsft_2-1733721679478.png

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.

vzhangtinmsft_3-1733721971788.png

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.

 

 

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

juliopatra
New Member

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

Thank you!!!

SaiTejaTalasila
Super User
Super User

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 

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

Thank you!!

SamInogic
Super User
Super User

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!

 

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.