How can I build a relative comparison between rows?

[MikeO]

I am tracking our advertisements by start and end date but to determine end date I need to determine start date of the next time an advertisement runs in same the advertising source.

I have already built a ranking column called "run number" where the latest run date is numbered 1 and the oldest run date is the highest value.

I have tried the following formula and while I get an end date whenever the run number is 1, I just get a blank for the rest of the run numbers:

End Date = IF(Table1[Run Number]=1,TODAY(),CALCULATE(MAX(Table1[Adjusted Run Date]),FILTER(Table1,Table1[Run Number]=Table1[Run Number]-1&&Table1[Newspaper]=EARLIER(Table1[Newspaper]))))

I have provided a table below (I have inserted what I want to appear in the end date column:)

Source	Creative Name	Adjusted Run Date	Run Number	End Date
A	H	12/31/2016	4	2/10/2017
A	L	2/10/2017	3	3/24/2017
A	H	3/24/2017	2	4/7/2017
A	H	4/7/2017	1	5/12/2018
B	L	2/12/2017	7	3/7/2017
B	H	3/7/2017	6	3/12/2017
B	W	3/12/2017	5	3/19/2017
B	L	3/19/2017	4	4/4/2017
B	K	4/4/2017	3	4/18/2017
B	W	4/18/2017	2	5/4/2017
B	K	5/4/2017	1	5/12/2018
C	Y	2/13/2017	5	2/20/2017
C	W	2/20/2017	4	3/16/2017
C	L	3/16/2017	3	3/17/2017
C	H	3/17/2017	2	3/20/2017
C	W	3/20/2017	1	5/12/2018
D	H	1/22/2018	8	2/13/2018
D	T	2/13/2018	7	2/22/2018
D	T	2/22/2018	6	2/26/2018
D	H	2/26/2018	5	2/26/2018
D	T	2/26/2018	4	2/27/2018
D	T	2/27/2018	3	3/5/2018
D	H	3/5/2018	2	3/5/2018
D	W	3/5/2018	1	5/12/2018

I have even tried using the following LOOKUPVALUE formula and receive an error message saying "The number of arguments is invalid.  Function LOOKUPVALUE must have a value for each specified column reference."

End Date = IF('NP Orders'[Run Number]=1,TODAY(),LOOKUPVALUE('NP Orders'[Adjusted Run Date],'NP Orders'[Run Number],'NP Orders'[Run Number]-1,'NP Orders'[Newspaper]=MAX('NP Orders'[Newspaper])))

Any help would be much appreciated!

[v-yulgu-msft · Accepted solution]

Hi @MikeO,

Please refer to below formula:

End Date =
IF (
    'Table1'[Run Number] = 1,
    TODAY (),
    LOOKUPVALUE (
        'Table1'[Adjusted Run Date],
        'Table1'[Source], 'Table1'[Source],
        'Table1'[Run Number], 'Table1'[Run Number] - 1
    )
)

Best regards,

Yuliana Gu

[MikeO]

Thank you, Yuliana.

That formula worked perfectly.

[MikeO]

I am tracking our advertisements by start and end date but to determine end date I need to determine start date of the next time an advertisement runs in same the advertising source.

I have already built a ranking column called "run number" where the latest run date is numbered 1 and the oldest run date is the highest value.

I have tried the following formula and while I get an end date whenever the run number is 1, I just get a blank for the rest of the run numbers:

End Date = IF(Table1[Run Number]=1,TODAY(),CALCULATE(MAX(Table1[Adjusted Run Date]),FILTER(Table1,Table1[Run Number]=Table1[Run Number]-1&&Table1[Newspaper]=EARLIER(Table1[Newspaper]))))

I have provided a table below (I have inserted what I want to appear in the end date column.

I have even tried using the following LOOKUPVALUE formula and receive an error message saying "The number of arguments is invalid. Function LOOKUPVALUE must have a value for each specified column reference."

End Date = IF('NP Orders'[Run Number]=1,TODAY(),LOOKUPVALUE('NP Orders'[Adjusted Run Date],'NP Orders'[Run Number],'NP Orders'[Run Number]-1,'NP Orders'[Newspaper]=MAX('NP Orders'[Newspaper])))

Any help would be much appreciated!

[MikeO]

It looks like this post was first flagged as spam, which has since been corrected although it also appears to have made it miss the attention of everyone.  If anyone can help with this question that would be much appreciated!

[Anonymous]

There is something missing in your simple data. What is the data in [Newspaper]?

[MikeO]

Sorry for the confusion, Newspaper was an earlier name I had for the advertising source ("Source").  Newspaper should be replaced with Source wherever it appears.