Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!!
Solved! Go to 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])
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
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
thanks for your help on this!!
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])
thanks for your help on this one!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |