Pull data from two rows to compare dates and service identifiers and return true or false

[mattyj2009]

Good Afternoon,

I have a report I'm needing to see if a service just moved from one account to another like changing ownership.  So if the connect date and the disconnect date are the same for the same service identifier then we would know it was a change of ownership.  I have sample data below.

Account Number	Connect Date	Disconnect Date	Service Identifier
000481987	11/8/2014	12/2/2017	7204676600
000382670	9/3/2013		6626660591
000484537	8/29/2017	11/19/2017	4210660384
000433763	12/2/2017		7204676600
000390250	11/19/2017		4210660384
000369719	9/4/2017		5737556415

So in the sample data above we can see that Service Identifier 7204676600 was disconnected on account 000481987 and was moved over to account 000433763.  Also Service Identifier 4210660384 was disconnected on account 000484537 and connected on account 000390250.  I would just need a column that states that the condition is either true or false.  Then I can just filter from that.  Any help would be appeciated.  Have a great day everyone!!!

[Greg_Deckler · Accepted solution]

Perhaps something along these lines:

Column = VAR previous = MAXX(FILTER(Services,[Service Identifier]=EARLIER(Services[Service Identifier]) && [Connect Date]=EARLIER([Disconnect Date]) && [Account Number]<>EARLIER(Services[Account Number])),Services[Connect Date]) RETURN IF(ISBLANK(previous),FALSE(),TRUE())