Trying to use MAX and IF functions (like in Excel) based on another column

[danielgibbs]

Hi Everyone,

Trying to use MAX and IF functions(Based in excel) within a table to find a Date value (MAX_DATE) in a custom column. I want Max date based on AssetID to populate in the MaxDate column.

Here's an example of the dataset.

ASSET_ID	END_DATE	MAX_DATE
100	6/30/2017	9/1/2019
100	6/30/2017	9/1/2019
100	9/1/2019	9/1/2019
100	6/30/2017	9/1/2019
101	6/30/2017	11/1/2019
101	11/1/2019	11/1/2019
101	6/30/2018	11/1/2019
102	6/30/2017	3/1/2020
102	3/1/2020	3/1/2020
102	6/30/2017	3/1/2020
102	9/1/2019	3/1/2020
103	12/31/2019	12/31/2021
103	12/31/2021	12/31/2021

Thanks in advance for your help!!!

[Greg_Deckler · Accepted solution]

If you can use a Measure instead, this should be simply:

MAX_DATE = MAX(Assets[END_DATE])

Then just put your ASSET_ID and this measure into a table visualization.

Another way to go would be to create an ASSET_IDS table with unique asset ids and a custom column in that table like so:

MAX_DATE = MAXX(RELATEDTABLE(Assets),[END_DATE])

[Anonymous · Accepted solution]

Hi @danielgibbs,

In your scenario, please firstly open Query Editor and add an index column to your current table as shown in the following screenshot.


Secondly, create a new column using the formula below.

Max Date = CALCULATE(MAX(Table1[END_DATE]),FILTER(Table1,Table1[ASSET_ID]=EARLIEST(Table1[ASSET_ID])))

Thirdly, create a table visual using all the fields. For more details, you can check the example in the attached PBIX file.


Thanks,
Lydia Zhang

[danielgibbs]

thanks for your help on this!!

[Greg_Deckler · Accepted solution]

If you can use a Measure instead, this should be simply:

MAX_DATE = MAX(Assets[END_DATE])

Then just put your ASSET_ID and this measure into a table visualization.

Another way to go would be to create an ASSET_IDS table with unique asset ids and a custom column in that table like so:

MAX_DATE = MAXX(RELATEDTABLE(Assets),[END_DATE])

[danielgibbs]

thanks for your help on this one!!