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          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  

 

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

6 Replies

  • 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

  • Uzi2019's avatar
    Uzi2019
    Icon for Community Champion rankCommunity Champion
    2 years ago

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

      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's avatar
        Uzi2019
        Icon for Community Champion rankCommunity Champion
        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