Measuring our teams efficacy based on two dates inside of one table.

[TimothyJames]

Hello, we have Units that we repair and we're trying to measure how our efforts are helping us gain on the backlog we have.

The Gist of my data is like This

Ticket ID	Failure Date	Unit Details	Repair Date
1	9/25/2020	ABC123	10/9/2020
2	10/5/2020	JDK456	10/15/2020
3	10/25/2020	xyz789

The [Ticket ID] 3 has a blank repair date because it has not been repaired. We can assume Early November I would like an uptick in the chart I speak to below.

The intent is to then have a chart with an X-Axis related to Date to show the progress we're making on our backlog.

In this instance our Back Log is 1.

However, when I try to use Failure date as the axis I get this

September - 1 Failed 1 Repaired (Ticket 1)

October - 2 Failed 1 Repaired (Ticket 2 and 3)

November - 0 Failed 0 Repaired (Ticket 3 doesn't show because when it gets repaired it will fall back to October scoring)

Then if I use Repair Date I get the inverse

September - 0 Failed, 1 Repaired

October - 2 Failed, 1 Repaired

November - 1 Failed 1 Repaired (Assuming Ticket 3 gets repaired in November)

What I want is

September we have 1 failure and 0 repaired, so overall we're +1 (Bad)

End of October we have 2 failures and 2 repaired, so overall We're still at +1 (Because ticket 3)

November we have 0 failures and 1 repaired, resulting our backlog to 0.

[amitchandak · Accepted solution]

@TimothyJames , refer if my blog on HR(start Date, end date) can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

video :https://youtu.be/e6Y-l_JtCq4

[TimothyJames]

@amitchandak How would I modify this to account for something a bit more precise.

So in your example its hired employees and terminated employees, but in my example its Tickets.

But a unit could be repaired and then 'fixed' and fail again.

In your example it would be as if the same employee (new ID) got hired and terminated over and over again.

How could I adjust this accordingly.

[TimothyJames]

@amitchandak This worked! FYI the only thing that I had issues with is my dates had Date/time as the format and yours were just date. Not sure if that's worth recognizing in your previous posts, but other than that it worked wonderfully. Thank you so so much!