set "first seen" date based on previous records

[mudegowdrp]

Hi,

I have a dataset coming in every week and I have used merged queries to build treands graph to show new records added and see those records which are not resolved.  Please help how can I solve this.

Week 1

week 2

I need to add column saying First_Seen

So my result dataset should look like

IP                                        Vulnerability ID               Date(data exported date)                  First Seen Date

11.1.111.11                          1001                               2/1/2018                                            2/1/2018

11.1.111.12                          1002                               2/1/2018                                            2/1/2018

11.1.111.13                          1003                               2/1/2018                                            2/1/2018

11.1.111.14                          1004                               2/1/2018                                            2/1/2018

11.1.111.15                          1005                               2/1/2018                                            2/1/2018

11.1.111.16                          1006                               2/1/2018                                            2/1/2018

11.1.111.17                          1007                               2/1/2018                                            2/1/2018

11.1.111.18                          1008                               2/1/2018                                            2/1/2018

11.1.111.11                          1001                               2/8/2018                                            2/8/2018

11.1.111.11                          1004                               2/8/2018                                            2/8/2018

Same IP will have different Vulnerability ID and vice versa.

But if both are same and occured previously, set first seen date to first occured.

Please help.

Thanks

[Greg_Deckler]

See the technique here using EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[mudegowdrp]

@Greg_Deckler Thank you. But I did not get that.

I should create a column based on IP and Vulnerability ID. If dataset is having same record I should use the FIRST exported as first seen date. if not I should just use exported date.

Please help

[Greg_Deckler · Accepted solution]

OK, taking the example:

Uptime = 
VAR next = MINX(FILTER(Repairs,
                       Repairs[MachineName]=EARLIER(Repairs[MachineName]) && 
                       Repairs[RepairStarted]>EARLIER(Repairs[RepairStarted]) && 
                       Repairs[RepairType]<>"PM"
               ),Repairs[RepairStarted])
RETURN IF([RepairType]="PM", 0,IF(ISBLANK(next),
               DATEDIFF([RepairCompleted],NOW(),SECOND),
               DATEDIFF([RepairCompleted],next,SECOND)
           )
       )

Your first seen would probably look like this:

First Seen = 
VAR next = MINX(FILTER(Table,
                       Table[Vulnerability ID]=EARLIER( Table[Vulnerability ID])),
			Table[Date(data exported date)])

RETURN IF(ISBLANK(next), [Date(data exported date)],next)

So, what you are doing is creating a temp table inside of MINX that has all of your rows that have the same Vulnerability ID. Then you take the MIN value of the Data column. You return that value.

[mudegowdrp]

@Greg_Deckler Okay I'm trying this. Thank you for explaing in detail.

[Greg_Deckler]

No worries, hope you get there. If not, let me know.

[mudegowdrp]

@Greg_DecklerIt works fine. Thanks again 🙂

[Greg_Deckler]

Awesome! Sometimes you can run into trouble with the "X" versions of functions on very large data sets.