Count repeats in a time frame

Question

I have two tables, one of failures, one of repairs, related on EquipmentName. I'm trying to find out if a given piece of equipment failed in the year after it was repaired.

RepairTable

EquipmentArea	EquipmentName	RepairDate
North	Switch1	1/1/2020
North	Switch2	2/1/2020
South	Switch3	3/1/2020
East	Switch4	4/1/2020
South	Switch5	5/1/2020
South	Switch6	6/1/2020

FailureTable

EquipmentName	FailureDate
Switch1	6/1/2020
Switch4	8/1/2020
Switch2	1/1/2021
Switch1	5/1/2021
Switch3	8/1/2021

Whatever solution makes most sense - a measure, a calculated column - should let me filter visuals by whether a piece of equipment failed in the first year after repair. Examples might look like:

Problem Equipment

Switch1

Switch2

Or

EquipmentArea	Failed First Year
North	2

I have a calculated column counting the total number of failures after the repair date, but I don't know how to add a time frame to that:

FailuresSinceRepair =

IF(ISBLANK(RepairTable[RepairDate),0,

COUNTX(

FILTER(

RELATEDTABLE(FailureTable),RepairTable[RepairDate]+1<=FailureTable[FailureDate]),FailureTable[FailureDate])+0)

amitchandak · Accepted Answer

ReadTheIron , New column in repair table 
&nbsp;
In next 1 year =
var _cnt = countx(filter(FailureTable, FailureTable[EquipmentName] =RepairTable[EquipmentName] &amp;&amp; FailureTable[FailureDate] &gt;=RepairTable[RepairDate] &amp;&amp; FailureTable[FailureDate] &lt;= RepairTable[RepairDate] +365) ,FailureTable[EquipmentName])
return
if(isblank(_cnt), "No", "Yes")&nbsp;

Forum Discussion

Count repeats in a time frame

2 Replies

Related content

Count repeating values

Paginated Report headers not repeating

Counting nulls

Counting repeat customers

Count repeating values