Forum Discussion

antoniopgouveia's avatar
antoniopgouveia
Frequent Visitor
8 years ago
Solved

Find next value by date

Hi all,   I have created an index for a table of opportunities which have an associated date for each entry. I have, then, concatenated the index with the dates (format: YYmmdd) to create my IndexD...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    antoniopgouveia

     

    Try with following

     

    Column =
VAR temp =
    TOPN (
        1,
        FILTER ( Table1, [Index] = EARLIER ( [Index] ) && [Date] > EARLIER ( [Date] ) ),
        [Date], ASC
    )
VAR result =
    MINX ( temp, [IndexDate] )
RETURN
    IF ( ISBLANK ( result ), [IndexDate], result )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

antoniopgouveia

 

Try with following

 

Column =
VAR temp =
    TOPN (
        1,
        FILTER ( Table1, [Index] = EARLIER ( [Index] ) && [Date] > EARLIER ( [Date] ) ),
        [Date], ASC
    )
VAR result =
    MINX ( temp, [IndexDate] )
RETURN
    IF ( ISBLANK ( result ), [IndexDate], result )
antoniopgouveia's avatar
antoniopgouveia
Frequent Visitor
8 years ago

Hi Zubair_Muhammad,

 

Thanks for that, it worked perfectly! I just had the filter because it was messing with my index column in relation to original Opportunity ID. They weren't matching to same opportunity ID. As below:

 

NextIndexDate =
VAR temp =
    TOPN (
        1,
        FILTER ( Table1, [Index] = EARLIER ( [Index] ) && [Date] > EARLIER ( [Date] ) && [OpportunityID] = [OpportunityID]),
        [Date], ASC
    )
VAR result =
    MINX ( temp, [IndexDate] )
RETURN
    IF ( ISBLANK ( result ), [IndexDate], result )

 Best regards,

Antonio