Forum Discussion
Helper IIPull data from two rows to compare dates and service identifiers and return true or false
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!!!
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())
1 Reply
- Greg_Deckler
Community Champion
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())