Find next value by date

[antoniopgouveia]

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 IndexDate column. I would like to find the next IndexDate within the same opportunity (index) and if there is no later entries for a certain opportunity I would like to fill in with the same IndexDate. My desired output is shown in column named NextIndexDate. Is there anyway to do this?

Index	Date	IndexDate	NextIndexDate
1	20/07/2018	1180720	1180730
1	30/07/2018	1180730	1180806
1	06/08/2018	1180806	1180807
1	07/08/2018	1180807	1180807
2	07/05/2018	2180507	2180620
2	20/06/2018	2180620	2180715
2	15/07/2018	2180715	2180715

Best regards,

Antonio

[Zubair_Muhammad · Accepted solution]

@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]

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