Vulnerabilities closed report monthly or yearly

[king2005r]

Hi

I have a table with monthly vulnerabilities, Plugin ID, and Host IP, I need to do the below measurement  :

1- Any vulnerabilities don't exist in the last scan month vulnerabilities consider as closed in all previous months 

2- Any vulnerabilities exist in the last scan month consider as open in all previous months

Example table : 

Aug/2020

Plugin ID	Host	Open Date
10263	11.22.33.116	1/Aug/2020
10386	12.33.44.23	1/Aug/2020

Sep/2020

Plugin ID	Host	Open Date
10263	11.22.33.116	1/Sep/2020
10389	12.33.44.23	1/Sep/2020

Oct/2020

Plugin ID	Host	Open Date
10244	11.22.33.66	1/Oct/2020
10347	12.33.44.23	1/Oct/2020

I expect something like the below table :

Plugin ID	Host	Open Date	Repeated	Status	Count of Plugin vulnerability	Today
10263	11.22.33.116	1-Aug-20	Not repeated	Open	1	1-Jan-21
10386	12.33.44.23	1-Aug-20	Not repeated	Open	1	1-Jan-21
10263	11.22.33.116	1-Sep-20	repeated	Open	2	1-Jan-21
10389	12.33.44.23	1-Sep-20	Not repeated	Closed	1	1-Jan-21
10244	11.22.33.66	1-Oct-20	Not repeated	Closed	1	1-Jan-21
10347	12.33.44.23	1-Oct-20	Not repeated	Closed	1	1-Jan-21
10244	11.22.33.66	1-Nov-20	repeated	Closed	2	1-Jan-21
10347	12.33.44.23	1-Nov-20	repeated	Closed	2	1-Jan-21
10263	11.22.33.116	1-Dec-20	repeated	open	3	1-Jan-21
10386	12.33.44.23	1-Dec-20	repeated	open	2	1-Jan-21
10263	11.22.33.116	1-Jan-21	repeated	open	4	1-Jan-21
10386	12.33.44.23	1-Jan-21	repeated	open	3	1-Jan-21

[v-deddai1-msft]

Hi @king2005r ，

Is there any update?

Best Regards,

Dedmon Dai

[king2005r]

Hi @v-deddai1-msft 

Thank you very much for your interest and follow-up. Really appreciated

Unfortunately when I tried it on the actual data, the formula is good, but there are some errors that appear during the current month as closed with the current month, and I could not know the reason
But it is so far the best formula that has been used
thank you very much

[v-deddai1-msft]

Hi @king2005r ,

I am not getting you very well. Would you please show us the expected output based on your sample data?

Best Regards,

Dedmon Dai

[king2005r]

Hi @v-deddai1-msft 

issue related to duplicated Plugin ID if there duplication, but can be solved by adding other condeation

Can I add one condeation to your fourmla :

'Table'[Host] = 'Table'[Host]

[v-deddai1-msft · Accepted solution]

Hi @king2005r ,

Please try :

Status = IF(CALCULATE(MAX('Table'[Repeated]),FILTER('Table','Table'[Plugin ID] = EARLIER('Table'[Plugin ID])&&'Table'[Open Date] = 'Table'[Today]&&'Table'[Host] = EARLIER('Table'[Host]))) = "repeated","Open","Closed")

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

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @king2005r ,

Would you please try to use the following calculated column:

Status = IF(CALCULATE(MAX('Table'[Repeated]),FILTER('Table','Table'[Plugin ID] = EARLIER('Table'[Plugin ID])&&'Table'[Open Date] = 'Table'[Today])) = "repeated","Open","Closed")

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

Best Regards,

Dedmon Dai

[king2005r]

Hi @v-deddai1-msft 

Thanks you for your reply, but unfortunately all status become close, and in your example there error not applying the same wanted rule 😞

1- Any vulnerabilities don't exist in the last month scan (Today) vulnerabilities consider as closed status in all previous months

2- Any vulnerabilities are exist in the last month scan (Today) consider as open in all previous months

[amitchandak]

@king2005r , I am assuming this data is appended or or you can append in power Query, This will very similar my blog , https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

Retained and New are Open and Lost is closed

Example of your case

	Rec = countrows(Table)
	MTD = calculate([Rec],datesmtd('Date'[Date]))
	LMTD = calculate([Rec],DATESMTD(DATEADD('Date'[Date],-1,MONTH)))	
	Closed = if(ISBLANK([MTD]) && not(ISBLANK([LMTD])) , "Closed","Open")

This need plot like you have shown

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[king2005r]

Unfortunately i tried and it did not work

[amitchandak]

@king2005r , Create a measure like

Measure = if( ISBLANK(CALCULATE(COUNT('Table'[Plugin ID]), FILTER(ALLSELECTED('Table'), 'Table'[Open Date] ='Table'[Today] && 'Table'[Plugin ID] =MAX('Table'[Plugin ID]) ))), "Closed","Open")

[king2005r]

Hi @amitchandak  

Unfortunately all resoult become Closed even for current month

[AllisonKennedy]

@king2005r  Why do you only have one closed? I can't see 10244 (and many others) in the last month so shouldn't that be closed? 

[king2005r]

Yes, you are correct, kindly find the below updated sample 

Plugin ID	Host	Open Date	Repeated	Status	Count of Plugin vulnerability	Today
10263	11.22.33.116	1-Aug-20	Not repeated	Open	1	1-Jan-21
10386	12.33.44.23	1-Aug-20	Not repeated	Open	1	1-Jan-21
10263	11.22.33.116	1-Sep-20	repeated	Open	2	1-Jan-21
10389	12.33.44.23	1-Sep-20	Not repeated	Closed	1	1-Jan-21
10244	11.22.33.66	1-Oct-20	Not repeated	Closed	1	1-Jan-21
10347	12.33.44.23	1-Oct-20	Not repeated	Closed	1	1-Jan-21
10244	11.22.33.66	1-Nov-20	repeated	Closed	2	1-Jan-21
10347	12.33.44.23	1-Nov-20	repeated	Closed	2	1-Jan-21
10263	11.22.33.116	1-Dec-20	repeated	open	3	1-Jan-21
10386	12.33.44.23	1-Dec-20	repeated	open	2	1-Jan-21
10263	11.22.33.116	1-Jan-21	repeated	open	4	1-Jan-21
10386	12.33.44.23	1-Jan-21	repeated	open	3	1-Jan-21