First Expiry Date in a column

[Emma2]

Can anyone help with getting a first expiry date from a column. 

I have data set as below:

Name             Document      Expiry Date

Name 1          Doc 1              07/09/2025

Name 1          Doc 2              18/09/2026

Name 1          Doc 3              24/05/2025

Name 2          Doc 1              15/08/2024

Name 2          Doc 2              30/11/2023

Name 3          Doc 3              25/01/2025

And so on - I need to extract the first expiry date per each name and it states the doc. 

Can anyone help please?

Thanks  

[Uzi2019 · Accepted solution]

Hi @Emma2 

Please try below measure:

Exp date = CALCULATE(MIN('Table 1 (Pivot)'[Expiry Date]),ALLEXCEPT('Table 1 (Pivot)','Table 1 (Pivot)'[Name ]))

after adding to the table set Doc to First like below setting

If my post helps please give kudos and accept it as a solution!
thanks

[Uzi2019]

Hi @Emma2 
Please share the expected outcome like how data should look like. 

Expiry date is expected output or input????

Name             Document      Expiry Date

Name 1          Doc 1              07/09/2025

Name 1          Doc 2              18/09/2026

Name 1          Doc 3              24/05/2025

Name 2          Doc 1              15/08/2024

Name 2          Doc 2              30/11/2023

Name 3          Doc 3              25/01/2025

[Emma2]

Hi,

The result should then look as to below; so I need only to be able to see the first expiry date with the document for each Name.

Name 1       Doc 3              24/05/2025
Name 2       Doc 2              30/11/2023

Name 3       Doc 3              25/01/2025

Thank you for your help.

[Uzi2019 · Accepted solution]

Hi @Emma2 

Please try below measure:

Exp date = CALCULATE(MIN('Table 1 (Pivot)'[Expiry Date]),ALLEXCEPT('Table 1 (Pivot)','Table 1 (Pivot)'[Name ]))

after adding to the table set Doc to First like below setting

If my post helps please give kudos and accept it as a solution!
thanks

[Uzi2019 · Accepted solution]

HI @Emma2 
If you just want to show n table you dont need to create any calculation just simply modify the aggregation like below
For Document to First

For expiry to Earliest

see you will get the same desired result.

If my post helps please give kudos and accept it as a solution!
Thanks

[HotChilli]

It's better to show the desired result when you post a question.

Do you want the name, earliest expiry and then the document from the row that matches that date?

If so,

put name in a table, drag the date in and aggregate with Earliest, then create a measure:

MeasureT = 
var _minDate = MIN(TableL[Expiry Date])
RETURN
CALCULATE (
    MIN ( TableL[Document]),
    KEEPFILTERS( TableL[Expiry Date] = _minDate )
)

[Emma2]

Hi,

Thank you for you help. This works however only brings up 1 result.

I need to get the first expiry date for each name and stating the document.

So  if data was as below; I need to return the first doc to expire for Name 1, the first doc to expire for Name 2 and so on. 

Name             Document      Expiry Date

Name 1          Doc 1              07/09/2025

Name 1          Doc 2              18/09/2026

Name 1          Doc 3              24/05/2025

Name 2          Doc 1              15/08/2024

Name 2          Doc 2              30/11/2023

Name 3          Doc 3              25/01/2025

Thank you