Forum Discussion

Emma2's avatar
Emma2
Frequent Visitor
3 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

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

       

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