Return a different value based on MAX value

[Anonymous]

Hi all, 

I wonder if someone can lend a hand with pointing me in the direction of how to get a value which is associated with the maximum contract number.

My Calculated table to get and return the latest contract number is: 

LastContract =

VAR ContractNo = 'Table'[ContractCheck]

RETURN

MAXX(FILTER( ALL( 'Table' ) , 'Table'[ContractCheck] = ContractNo ), 'Table'[Contract no] )

Which seems to work at getting me the latest contract issue number, but associated to this is a date (which may or may not be earlier/later/the same); what I would like to do is have DAX return the associated date as part of another calculated column. I only want one value (the latest date on the contract) to be returned for each LastContract no. 

If it was just a case of looking at the MAX value again I can see how that would work, but because the date might be an earlier date I need for the formula to look at the LastContract and return the associated date.

Thanks

[Anonymous]

Can you try below as column or Measure

CALCULATE(

MIN('Table'[Date]),

ALLEXCEPT('Table','Table'[Name],'Table'[Head Contract No])

)

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

If you use column ,you could try it.

Column = IF([Date]=  CALCULATE(MIN([Date]),FILTER('Table',[Head Contract No]=EARLIER([Head Contract No]))),[Date],BLANK())

Or 

Column=CALCULATE(MIN([Date]),FILTER('Table',[Head Contract No]=EARLIER([Head Contract No])))

IF you want to create a calculate table.

New table = SUMMARIZE('Table',[Name],[Head Contract No],"Date1",CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Head Contract No]=EARLIER([Head Contract No]))))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Excellent - thank you!

[v-yalanwu-msft]

Hi, @Anonymous ;

You could create a measure.

Measure = CALCULATE(MIN('Table'[Date]),FILTER(ALLSELECTED('Table'),[Head Contract No]=MAX([Head Contract No])))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yalanwu-msft 

Excellent - that works! 

If I wanted to use this in a calculated column (so I could then apply it in other calculations for example) is there an easy way to do this? Or would it require a different approach? 

[amitchandak]

@Anonymous
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak Sorry for the delay - example data below:

Name	Head Contract No	Contract No	Date
Mr. Smith	100	0800	01/02/2022
Mr. Smith	100	0805	01/01/2022
Mr. Smith	101	0810	01/05/2022
Another person	105	1050	01/05/2023

In this example, I would like the results returned to be:

Mr Smith, 100, 01/01/2022

Mr Smith, 101, 01/05/2022

Another person, 105, 01/05/2023

So my code can figure out the contract no, and then return the highest head contract associated with it - but I need to figure out how to get the date from the most recent contract returned.

It may be something that I could approach from another angle, if so, any advice is welcome.