Check event occurred within set time of previous event of same unique ID

[KvO88]

I have a list with unique system ID's who require periodic Preventive Maintenance. I would like to know whether for that same system ID there has been a Corrective Maintenance within one month after the Preventive Maintenance. So in the example table below, I would like to identify the case types in red, those are corrective maintenance cases that occured within a month after a preventive maintenance. How do I make this identification?

System ID	Case Type	Date
1	Preventive Maintenance	01/Jan/24
2	Preventive Maintenance	01/Jan/24
3	Preventive Maintenance	01/Feb/24
4	Preventive Maintenance	01/Feb/24
1	Corrective Maintenance	25/Jan/24
5	Preventive Maintenance	01/Mar/24
6	Preventive Maintenance	01/Apr/24
2	Corrective Maintenance	02/Feb/24
3	Corrective Maintenance	17/Feb/24
7	Preventive Maintenance	01/Apr/24
8	Preventive Maintenance	01/May/24
9	Preventive Maintenance	01/May/24
5	Corrective Maintenance	02/Jun/24
6	Corrective Maintenance	08/Apr/24
6	Corrective Maintenance	12/Jun/24

[v-tianyich-msft · Accepted solution]

Hi @KvO88 ,

I apologize for ignoring the extra condition, please try the following expression:

Measure = var _t =ADDCOLUMNS('Table',"A",MINX(FILTER(ALL('Table'),[Case Type]="Preventive Maintenance"&&[System ID]=EARLIER([System ID])),[Date]))
var _t2 =DATEDIFF(MINX(_t,[A]),SELECTEDVALUE('Table'[Date]),DAY)
RETURN IF(_t2>0&&_t2<30&&MAX('Table'[Case Type])="Corrective Maintenance","red","")

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[KvO88]

@v-tianyich-msft Thanks so much, this already helps a lot. 

There is one thing that I still run in to, does the measure takes into account that I only want to see when a corrective maintenance is within a month after a preventive maintenance? 

It looks like it checks all lines, despite the Case type.  Thanks in advance for your help!

[v-tianyich-msft · Accepted solution]

Hi @KvO88 ,

I apologize for ignoring the extra condition, please try the following expression:

Measure = var _t =ADDCOLUMNS('Table',"A",MINX(FILTER(ALL('Table'),[Case Type]="Preventive Maintenance"&&[System ID]=EARLIER([System ID])),[Date]))
var _t2 =DATEDIFF(MINX(_t,[A]),SELECTEDVALUE('Table'[Date]),DAY)
RETURN IF(_t2>0&&_t2<30&&MAX('Table'[Case Type])="Corrective Maintenance","red","")

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[KvO88]

@v-tianyich-msft Thanks! This works 😃 One additional question, how can I convert this measure so that I get the counts of the number of Corrective Maintenances that occured within a month after Preventive?

[v-tianyich-msft]

Hi @KvO88 ,

Try the following expression:

Measure = var _t =ADDCOLUMNS('Table',"A",MINX(FILTER(ALL('Table'),[Case Type]="Preventive Maintenance"&&[System ID]=EARLIER([System ID])),[Date]))
var _t2 =DATEDIFF(MINX(_t,[A]),SELECTEDVALUE('Table'[Date]),DAY)
RETURN IF(_t2>0&&_t2<30&&MAX('Table'[Case Type])="Corrective Maintenance",COUNTROWS('Table'),"")

 Best regards,
Community Support Team_ Scott Chang

[KvO88]

@v-tianyich-msft Thank you for you quick replies! I really appreciate it. 

If I use a Card visual with this measure, I do not get a result....

[v-tianyich-msft · Accepted solution]

Hi @KvO88 ,

On top of that, add a measure:

Measure 2 = 
SUMX(ALLSELECTED('Table'),[Measure])

Best regards,
Community Support Team_ Scott Chang

[v-tianyich-msft]

Hi @KvO88 ,

I made simple samples and you can check the results below:

Measure = var _t =ADDCOLUMNS('Table',"A",MINX(FILTER(ALL('Table'),[System ID]=EARLIER([System ID])),[Date]))
var _t2 =DATEDIFF(MINX(_t,[A]),SELECTEDVALUE('Table'[Date]),DAY)
RETURN IF(_t2>0&&_t2<30,"red","")

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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