4 Replies Latest reply on Jul 18, 2018 3:05 AM by Mahfooj Khan

Finding Maximum of a Date column based on another column revenue which is not null

Hi,

Thanks for trying to help me.

I have two columns, first is date column which contains the list of dates of the entire financial year, the second column contains the revenue of each day. The revenue for future dates will be blank. Hence I need to find out the maximum of date for which the revenue is not null. Can you give me a calculated field formula to find this.

Thanks.

Anthony

150

DateRevenue
01/01/2018100
02/01/2018
03/01/2018

I need to pull value 02/01/2018 which is the latest date that contains revenue.

• 1. Re: Finding Maximum of a Date column based on another column revenue which is not null

There is a wrong in the table. Please read as 150 for 02/01/2018.

• 2. Re: Finding Maximum of a Date column based on another column revenue which is not null

Hi Anthony,

I have taken sample data Set as per your requirement as below

In order to get the maximum date use the below calculation

Mark this answer as correct or helpful if it helps to solve your problem

BR,

NB

• 3. Re: Finding Maximum of a Date column based on another column revenue which is not null

Check the below calc i have provide it will help to give the max date so that you can use that retrieve any value

• 4. Re: Finding Maximum of a Date column based on another column revenue which is not null

Hi,

Is that what you're looking for?

Option 1:

IF [Date]={MAX(IF NOT ISNULL([Revenue]) THEN [Date] END)}

THEN [Date]

END

Option 2:

{MAX(IF NOT ISNULL([Revenue]) THEN [Date] END)}

Let us know if this help.

Mahfooj