Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate Prior Date based on unique field

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 Da...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

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