Vulnerabilities status report

[king2005r]

Hi

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

1- Get repeated monthly vulnerabilities

2- Get the count of the new vulnerabilities

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

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

Uniq column are Plugin ID and Host IP 

I try the below Dax but doesn't give me the correct numbers as wanted 

Closed Vulnerabilites = if(ISBLANK(Maxx(FILTER('Vulnerabilites-Report','Vulnerabilites-Report'[Host]=EARLIER('Vulnerabilites-Report'[Host]) && 'Vulnerabilites-Report'[Plugin ID]=EARLIER('Vulnerabilites-Report'[Plugin ID]) && 'Vulnerabilites-Report'[Open Date].[MonthNo]> EARLIER('Vulnerabilites-Report'[Open Date].[MonthNo])),'Vulnerabilites-Report'[Open Date].[Date])),0,1)

Repeated Count New = if(ISBLANK(Maxx(FILTER('Vulnerabilites-Report','Vulnerabilites-Report'[Host]=EARLIER('Vulnerabilites-Report'[Host]) && 'Vulnerabilites-Report'[Plugin ID]=EARLIER('Vulnerabilites-Report'[Plugin ID]) && 'Vulnerabilites-Report'[Open Date].[MonthNo]< EARLIER('Vulnerabilites-Report'[Open Date].[MonthNo]) && DATEADD('Vulnerabilites-Report'[Open Date].[Date],-1,MONTH)),'Vulnerabilites-Report'[Open Date].[Date])),0,1)

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

[king2005r]

Hi @v-kelly-msft 

sorry for this, I want to have the below if you can support in this further :

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

[king2005r]

Hi @v-kelly-msft , thanks for the reply

If the today date is 1/1/2021

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	1	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	Open	1	1-Jan-21
10347	12.33.44.23	1-Oct-20	Not repeated	Open	1	1-Jan-21
10244	11.22.33.66	1-Nov-20	repeated	Open	2	1-Jan-21
10347	12.33.44.23	1-Nov-20	repeated	Open	2	1-Jan-21
10263	11.22.33.116	1-Dec-20	repeated	open	2	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	3	1-Jan-21
10386	12.33.44.23	1-Jan-21	repeated	open	3	1-Jan-21

[v-kelly-msft · Accepted solution]

Hi @king2005r ,

Create 2 calculated columns as below:

Repeated = 
 var _previous=CALCULATE(MAX('Table'[Plugin ID]),FILTER('Table','Table'[Host]=EARLIER('Table'[Host])&&'Table'[Open Date]<EARLIER('Table'[Open Date])&&'Table'[Plugin ID]=EARLIER('Table'[Plugin ID])))
 Return
 IF(_previous=BLANK(),"Not repeated","Repeated")

Count of Plugin vulnerability = 
var _count=CALCULATE(COUNT('Table'[Plugin ID]),FILTER('Table','Table'[Plugin ID]=EARLIER('Table'[Plugin ID])&&'Table'[Host]=EARLIER('Table'[Host])&&'Table'[Open Date]<=EARLIER('Table'[Open Date])))
Return
_count

And you wil see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[king2005r]

Thanks a lot @v-kelly-msft 

[v-kelly-msft]

Hi @king2005r ,

What is your expected output based on your sample data?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@king2005r , Based on first look very similar to customer retention problem. I have couple of blog on that. Refer if these can help

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

[king2005r]

Thanks a lot for your reply, but unfortunately, using a new formula is not currently possible. If it is possible to modify the existing formula, this will be the best and fastest solution instead of rebuilding all the formulas from the beginning