Forum Discussion
Calculated column that compares multiple columns and returns latest date
Hi all,
I have a data set that is set-up with multiple records that had various changes and addendums to them (changes to rates, duration, etc) they are all identified by a CTR No. though.
I wondered if there was a way that I could use DAX to draw out the various CTR No. and only return the latest date in each deadline column (currently I get multiple rows as opposed to just the final 'end' date).
The data looks roughtly like this:
| Start | Deadline | CTR No | Head CTR No | |
| 01/01/2020 | 31/08/2021 | 5986 | Date change | 5702 |
| 01/01/2020 | 30/06/2021 | 5875 | Rates error | 5702 |
| 01/01/2020 | 31/03/2021 | 5702 |
How do I go about just getting one result back for dates on 5702 that is 31/08/2021?
Thanks
Anonymous , You can get the latest value on CTR No
measure
latest price =
VAR __id = MAX ('Table'[CTR No] )
VAR __date = CALCULATE ( MAX('Table'[Deadline] ), ALLSELECTED ('Table' ), 'Table'[CTR No] = __id )
CALCULATE ( MAX ('Table'[Head CTR No] ), VALUES ('Table'[CTR No] ),'Table'[CTR No] = __id,'Table'[Deadline] = __date )
2 Replies
- amitchandak
Super User
Anonymous , You can get the latest value on CTR No
measure
latest price =
VAR __id = MAX ('Table'[CTR No] )
VAR __date = CALCULATE ( MAX('Table'[Deadline] ), ALLSELECTED ('Table' ), 'Table'[CTR No] = __id )
CALCULATE ( MAX ('Table'[Head CTR No] ), VALUES ('Table'[CTR No] ),'Table'[CTR No] = __id,'Table'[Deadline] = __date )