Vulnerabilities status report monthly (Help)

[king2005r]

Dear All,

I hope you have a great day!

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

2- Get the count of the new vulnerabilities

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

4- Any vulnerabilities that exist in the last month result to be considered 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

Sample of the output :

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

[king2005r]

Hi @v-yalanwu-msft 

any idea please. 

[king2005r]

Up !

[v-yalanwu-msft]

Hi @king2005r ,

From your description, If you have three tables, you can first append them in power query.

Then create a column to output last month, next create a measure with swicth(), and finally count by Plugin ID.
the following formula to create a measure or column :
First: create a column:

lastmonth =
VAR _a = 'Table'[Plugin ID]
VAR _b = 'Table'[Open Date]
RETURN
MAXX ( FILTER ( 'Table', _a = [Plugin ID] && _b > [Open Date] ), [Open Date] )

Then create a measure:

Status =
SWITCH (
TRUE (),
MAX ( 'Table'[Open Date] ) = MINX ( ALL ( 'Table' ), [Open Date] ), "Open",
MONTH ( MAX ( 'Table'[Open Date] ) - MAX ( 'Table'[lastmonth] ) ) = 1, "Open",
"Close")

Last count: create a measure:

Count of Plugin vulnerability = var a= MAX('Table'[Open Date])
return COUNTROWS(FILTER(ALL('Table'),[Plugin ID]=MAX('Table'[Plugin ID])&&[Open Date]<=a))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[king2005r]

Hi @v-yalanwu-msft 

Thank you so much, i tried your solutions but all status become closed

Unfortunately, I expect that the problem is occurring because of that need to compare 2 cloumn (Plugin id, host name ) must be the same for the last month and prevous month (plugin id can be dublicated with other host name, but the host name will be uniq) to be open

lastmonth =
VAR _a = 'Table'[Plugin ID]
VAR _b = 'Table'[Open Date]
RETURN
MAXX ( FILTER ( 'Table', _a = [Plugin ID] && _b > [Open Date] ), [Open Date] )