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:) SourceCreative NameAdjusted Run DateRun NumberEnd DateAH12/31/201642/10/2017AL2/10/201733/24/2017AH3/24/201724/7/2017AH4/7/201715/12/2018BL2/12/201773/7/2017BH3/7/201763/12/2017BW3/12/201753/19/2017BL3/19/201744/4/2017BK4/4/201734/18/2017BW4/18/201725/4/2017BK5/4/201715/12/2018CY2/13/201752/20/2017CW2/20/201743/16/2017CL3/16/201733/17/2017CH3/17/201723/20/2017CW3/20/201715/12/2018DH1/22/201882/13/2018DT2/13/201872/22/2018DT2/22/201862/26/2018DH2/26/201852/26/2018DT2/26/201842/27/2018DT2/27/201833/5/2018DH3/5/201823/5/2018DW3/5/201815/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!

Helper I

8 years ago

Solved

How can I build a relative comparison between rows?

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!

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

6 Replies

v-yulgu-msft
Microsoft Employee
8 years ago
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
  Helper I
  8 years ago
  Thank you, Yuliana.
  
  That formula worked perfectly.
MikeO
Helper I
8 years ago
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
  Helper I
  8 years ago
  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
    Not applicable
    8 years ago
    There is something missing in your simple data. What is the data in [Newspaper]?

Forum Discussion

How can I build a relative comparison between rows?