Systems missing patches for multiple months

[prj102]

Need a bit of help.  I'm trying to find the best way to calculate if a system is missing patches from multiple months.  Consecutive and non-consecutive.  

I'm using SCCM to pull in patch information with patch status (compliant or not) and I'm hoping to create two columns that will look for patching missing from multiple months.  

Any ideas would be helpful.

Update - More info:

My patch info table looks something like this.  

machineid	ci_id	status	PatchDate
system1	patch1	compliant	3/15/2019
system2	patch1	not compliant	3/15/2019
system3	patch1	not compliant	3/15/2019
system1	patch2	compliant	2/13/2019
system2	patch2	not compliant	2/13/2019
system3	patch2	compliant	2/13/2019
system1	patch3	compliant	1/9/2019
system2	patch3	compliant	1/9/2019
system3	patch3	not compliant	1/9/2019

I'm looking to create a calculated column that will tell me something like the following.

machineid	status
system1	compliant
system2	missing consecutive months
system3	missing multiple months

[v-shex-msft]

Hi @prj102 ,

In my opinion, I'd like to suggest you except function to compare with current list and full list. Can you please share some sample data with expect result?

Notice: do mask on sensitive data.

Regards,

Xiaoxin Sheng

[prj102]

Updated with an example.

[v-shex-msft]

HI @prj102 ,

You can try to use following measure formula:

Measure =
VAR _statuscount =
    CALCULATE (
        COUNTA ( T1[status] ),
        FILTER ( ALLSELECTED ( T1 ), [status] = "compliant" ),
        VALUES ( T1[machineid] )
    )
VAR _datecount =
    COUNTROWS ( VALUES ( T1[PatchDate] ) )
RETURN
    IF (
        ISINSCOPE ( T1[machineid] ),
        IF (
            _statuscount = _datecount,
            "compliant",
            "Miss " & _datecount - _statuscount & " Patchs"
        )
    )

Regards,

Xiaoxin Sheng

[prj102]

That's not really working.  Everything is coming back blank.  I had to change a bit to work with my data.  value 3 for status is compliant.

Measure = 
VAR _statuscount = 
    CALCULATE ( 
        COUNTA ( ComputerUpdate[status] ), 
        FILTER ( ALLSELECTED ( ComputerUpdate ), [status] = 3 ), 
        VALUES ( ComputerUpdate[machineid] ) 
    ) 
VAR _datecount = 
    COUNTROWS ( VALUES ( ComputerUpdate[PatchDate] ) ) 
RETURN 
    IF ( 
        ISINSCOPE ( ComputerUpdate[machineid] ), 
        IF ( 
            _statuscount = _datecount, 
            "compliant", 
            "Miss " & _datecount - _statuscount & " Patches" 
        ) 
    )