Calculate Prior Date based on unique field

Question

Hello,&nbsp;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.&nbsp;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.&nbsp;&nbsp;Customer_IDContractEffDtMapPlan NamePlanLinkEndDate2990916907601/01/14D0012737Bronze (PPO)0169076D0012737Bronze (PPO)12/31/142990916907601/01/15D0012737Bronze (PPO)0169076D0012737Bronze (PPO)12/31/152990916907601/01/16D0012737Bronze (PPO)0169076D0012737Bronze (PPO)12/31/162990916907601/01/17D0012737Bronze (PPO)0169076D0012737Bronze (PPO)12/31/992990916907601/01/14D0012738Silver (PPO)0169076D0012738Silver (PPO)12/31/142990916907601/01/15D0012738Silver (PPO)0169076D0012738Silver (PPO)12/31/152990916907601/01/16D0012738Silver (PPO)0169076D0012738Silver (PPO)12/31/162990916907601/01/17D0012738Silver (PPO)0169076D0012738Silver (PPO)12/31/992990916907601/01/14D0012739Gold (PPO)0169076D0012739Gold (PPO)12/31/142990916907601/01/15D0012739Gold (PPO)0169076D0012739Gold (PPO)12/31/152990916907601/01/16D0012739Gold (PPO)0169076D0012739Gold (PPO)12/31/162990916907601/01/17D0012739Gold (PPO)0169076D0012739Gold (PPO)12/31/992990916907601/01/16D0012745Platinum (DHMO)0169076D0012745Platinum (DHMO)12/31/162990916907601/01/17D0012745Platinum (DHMO)0169076D0012745Platinum (DHMO)12/31/992990916907601/01/16D0012751Platinum (DHMO)0169076D0012751Platinum (DHMO)12/31/162990916907601/01/17D0012751Platinum (DHMO)0169076D0012751Platinum (DHMO)12/31/992990916907601/01/16D0012794Platinum (DHMO)0169076D0012794Platinum (DHMO)12/31/162990916907601/01/17D0012794Platinum (DHMO)0169076D0012794Platinum (DHMO)12/31/992990916907601/01/16D0012795Platinum (DHMO)0169076D0012795Platinum (DHMO)12/31/162990916907601/01/17D0012795Platinum (DHMO)0169076D0012795Platinum (DHMO)12/31/992990916907601/01/16D0013549Platinum (DHMO)0169076D0013549Platinum (DHMO)12/31/162990916907601/01/17D0013549Platinum (DHMO)0169076D0013549Platinum (DHMO)12/31/9925446622770401/01/16D0012737Bronze (PPO)0227704D0012737Bronze (PPO)12/31/9925446622770401/01/16D0012738Silver (PPO)0227704D0012738Silver (PPO)12/31/9925357624171401/01/07D0012739Gold (PPO)0241714D0012739Gold (PPO)12/31/0725357624171401/01/08D0012739Gold (PPO)0241714D0012739Gold (PPO)12/31/996188524235901/01/18D0012737Bronze (PPO)0242359D0012737Bronze (PPO)12/31/176188524235901/01/18D0012738Silver (PPO)0242359D0012738Silver (PPO)&nbsp;6188524235901/01/18D0012739Gold (PPO)0242359D0012739Gold (PPO)&nbsp;6188524235901/01/18D0012745Platinum (DHMO)0242359D0012745Platinum (DHMO)&nbsp;6188524235901/01/18D0012751Platinum (DHMO)0242359D0012751Platinum (DHMO)&nbsp;6188524235901/01/18D0012794Platinum (DHMO)0242359D0012794Platinum (DHMO)&nbsp;6188524235901/01/18D0012795Platinum (DHMO)0242359D0012795Platinum (DHMO)&nbsp;6188524235901/01/18D0013549Platinum (DHMO)0242359D0013549Platinum (DHMO)&nbsp;2451970111501/01/05D0012737Bronze (PPO)0701115D0012737Bronze (PPO)&nbsp;2451970111501/01/05D0012738Silver (PPO)0701115D0012738Silver (PPO)&nbsp;2451970111501/01/05D0012739Gold (PPO)0701115D0012739Gold (PPO)&nbsp;15725270414004/01/16D0012737Bronze (PPO)0704140D0012737Bronze (PPO)&nbsp;15725270414001/01/17D0012737Bronze (PPO)0704140D0012737Bronze (PPO)&nbsp;15725270414001/01/18D0012737Bronze (PPO)0704140D0012737Bronze (PPO)&nbsp;15725270414004/01/16D0012738Silver (PPO)0704140D0012738Silver (PPO)&nbsp;15725270414001/01/17D0012738Silver (PPO)0704140D0012738Silver (PPO)&nbsp;15725270414001/01/18D0012738Silver (PPO)0704140D0012738Silver (PPO)&nbsp;15725270414004/01/16D0012739Gold (PPO)0704140D0012739Gold (PPO)&nbsp;

v-cherch-msft · Accepted Answer

Hi&nbsp;Anonymous
&nbsp;
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
)

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

&nbsp;

&nbsp;
Regards,
Cherie

Forum Discussion

Calculate Prior Date based on unique field

1 Reply

Related content

Remaining Balance Depends on Prior Month

How do you calculate prior year values?

Unique Count in Row convert

Unique Average Calculation

unique calculated column

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)