Calculate Prior Date based on unique field

[Anonymous]

Hello,

In the list below, for each row, I need to find the next effective date (effdt) based [PlanLink], sort of like a sumifs in Excel. What I am trying to do is get each effective date's End Date by finding its next effective date and subtracting a day. In the instances were the latest plan is still the most recent I just want to set the end date as some far off date like 12/31/2099 or something.

I have included a live data set as well as manually assigned the first several rows on [EndDate] to illustrate what I am trying to do. Again, each EndDate should be looking at EffDtg and unique [PlanLink] fields.

Customer_ID	Contract	EffDt	Map	Plan Name	PlanLink	EndDate
29909	169076	01/01/14	D0012737	Bronze (PPO)	0169076D0012737Bronze (PPO)	12/31/14
29909	169076	01/01/15	D0012737	Bronze (PPO)	0169076D0012737Bronze (PPO)	12/31/15
29909	169076	01/01/16	D0012737	Bronze (PPO)	0169076D0012737Bronze (PPO)	12/31/16
29909	169076	01/01/17	D0012737	Bronze (PPO)	0169076D0012737Bronze (PPO)	12/31/99
29909	169076	01/01/14	D0012738	Silver (PPO)	0169076D0012738Silver (PPO)	12/31/14
29909	169076	01/01/15	D0012738	Silver (PPO)	0169076D0012738Silver (PPO)	12/31/15
29909	169076	01/01/16	D0012738	Silver (PPO)	0169076D0012738Silver (PPO)	12/31/16
29909	169076	01/01/17	D0012738	Silver (PPO)	0169076D0012738Silver (PPO)	12/31/99
29909	169076	01/01/14	D0012739	Gold (PPO)	0169076D0012739Gold (PPO)	12/31/14
29909	169076	01/01/15	D0012739	Gold (PPO)	0169076D0012739Gold (PPO)	12/31/15
29909	169076	01/01/16	D0012739	Gold (PPO)	0169076D0012739Gold (PPO)	12/31/16
29909	169076	01/01/17	D0012739	Gold (PPO)	0169076D0012739Gold (PPO)	12/31/99
29909	169076	01/01/16	D0012745	Platinum (DHMO)	0169076D0012745Platinum (DHMO)	12/31/16
29909	169076	01/01/17	D0012745	Platinum (DHMO)	0169076D0012745Platinum (DHMO)	12/31/99
29909	169076	01/01/16	D0012751	Platinum (DHMO)	0169076D0012751Platinum (DHMO)	12/31/16
29909	169076	01/01/17	D0012751	Platinum (DHMO)	0169076D0012751Platinum (DHMO)	12/31/99
29909	169076	01/01/16	D0012794	Platinum (DHMO)	0169076D0012794Platinum (DHMO)	12/31/16
29909	169076	01/01/17	D0012794	Platinum (DHMO)	0169076D0012794Platinum (DHMO)	12/31/99
29909	169076	01/01/16	D0012795	Platinum (DHMO)	0169076D0012795Platinum (DHMO)	12/31/16
29909	169076	01/01/17	D0012795	Platinum (DHMO)	0169076D0012795Platinum (DHMO)	12/31/99
29909	169076	01/01/16	D0013549	Platinum (DHMO)	0169076D0013549Platinum (DHMO)	12/31/16
29909	169076	01/01/17	D0013549	Platinum (DHMO)	0169076D0013549Platinum (DHMO)	12/31/99
254466	227704	01/01/16	D0012737	Bronze (PPO)	0227704D0012737Bronze (PPO)	12/31/99
254466	227704	01/01/16	D0012738	Silver (PPO)	0227704D0012738Silver (PPO)	12/31/99
253576	241714	01/01/07	D0012739	Gold (PPO)	0241714D0012739Gold (PPO)	12/31/07
253576	241714	01/01/08	D0012739	Gold (PPO)	0241714D0012739Gold (PPO)	12/31/99
61885	242359	01/01/18	D0012737	Bronze (PPO)	0242359D0012737Bronze (PPO)	12/31/17
61885	242359	01/01/18	D0012738	Silver (PPO)	0242359D0012738Silver (PPO)
61885	242359	01/01/18	D0012739	Gold (PPO)	0242359D0012739Gold (PPO)
61885	242359	01/01/18	D0012745	Platinum (DHMO)	0242359D0012745Platinum (DHMO)
61885	242359	01/01/18	D0012751	Platinum (DHMO)	0242359D0012751Platinum (DHMO)
61885	242359	01/01/18	D0012794	Platinum (DHMO)	0242359D0012794Platinum (DHMO)
61885	242359	01/01/18	D0012795	Platinum (DHMO)	0242359D0012795Platinum (DHMO)
61885	242359	01/01/18	D0013549	Platinum (DHMO)	0242359D0013549Platinum (DHMO)
24519	701115	01/01/05	D0012737	Bronze (PPO)	0701115D0012737Bronze (PPO)
24519	701115	01/01/05	D0012738	Silver (PPO)	0701115D0012738Silver (PPO)
24519	701115	01/01/05	D0012739	Gold (PPO)	0701115D0012739Gold (PPO)
157252	704140	04/01/16	D0012737	Bronze (PPO)	0704140D0012737Bronze (PPO)
157252	704140	01/01/17	D0012737	Bronze (PPO)	0704140D0012737Bronze (PPO)
157252	704140	01/01/18	D0012737	Bronze (PPO)	0704140D0012737Bronze (PPO)
157252	704140	04/01/16	D0012738	Silver (PPO)	0704140D0012738Silver (PPO)
157252	704140	01/01/17	D0012738	Silver (PPO)	0704140D0012738Silver (PPO)
157252	704140	01/01/18	D0012738	Silver (PPO)	0704140D0012738Silver (PPO)
157252	704140	04/01/16	D0012739	Gold (PPO)	0704140D0012739Gold (PPO)

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may add an index column in query editor first. Then you may create the below calculated columns.

Group =
IF (
    Table4[PlanLink]
        = LOOKUPVALUE ( Table4[PlanLink], Table4[Index], Table4[Index] + 1 ),
    0,
    1
)

EndDate =
IF (
    Table4[Group] = 0,
    LOOKUPVALUE ( Table4[EffDt], Table4[Index], Table4[Index] + 1 )
        - 1,
    DATEVALUE ( "12/31/2099" )
)

Regards,

Cherie