Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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) |
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |