Forum Discussion

Emma2's avatar
Emma2
Frequent Visitor
2 years ago
Solved

First Expiry Date in a column

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   ...
Need Help
  • Uzi2019's avatar
    Uzi2019
    2 years ago

    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's avatar
    Uzi2019
    2 years ago

    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's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
    Emma2
    Frequent Visitor
    2 years ago

    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