Global Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Need Help finding Max date in a row

  • 1.  Need Help finding Max date in a row

    Posted Feb 16, 2019 10:52 PM

    I would like to get the max date for each row. Can anyone please help
    Name Date1 Date2 Date3 Date4 Date5 Max Date
    A1 1/1/2017 1/1/2018 1/1/2019 1/1/2020 1/1/2021  
    A2 1/1/2018 1/1/2019 1/1/2020 1/1/2021 1/1/2022  
    A3 1/2/2018 1/2/2019 1/2/2020 1/2/2021 1/1/2023  
    A4 1/3/2018 1/3/2019 1/3/2020 1/3/2021 1/1/2024  
    A5 1/4/2018 1/4/2019 1/4/2020 1/4/2021 1/1/2025  
    A6 1/5/2018 1/5/2019 1/5/2020 1/5/2021 1/1/2026  
    A7 1/6/2018 1/6/2019 1/6/2020 1/6/2021 1/1/2027  
    A8 1/7/2018 1/7/2019 1/7/2020 1/7/2021 1/1/2028  
    A9 1/8/2018 1/8/2019 1/8/2020 1/8/2021 1/1/2029  
    A10 1/9/2018 1/9/2019 1/9/2020 1/9/2021 1/1/2030  
    A11 1/10/2018 1/10/2019 1/10/2020 1/10/2021 1/1/2031  
    A12 1/11/2018 1/11/2019 1/11/2020 1/11/2021 1/1/2032  
    A13 1/12/2018 1/12/2019 1/12/2020 1/12/2021 1/1/2033  
    A14 1/13/2018 1/13/2019 1/13/2020 1/13/2021 1/1/2034  


    ------------------------------
    Kaylan G
    Manufacturing

    ------------------------------


  • 2.  RE: Need Help finding Max date in a row

    Top Contributor
    Posted Feb 18, 2019 04:11 AM
      |   view attached
    Hi @Kaylan G,

    Please let me know if this is the solution that you are looking for:


    I have used the following code:
    Max Date = 
    MAX(
        MAX(Table1[Date1]),
    MAX(
        MAX(Table1[Date2]),
    MAX(
        MAX(Table1[Date3]),
    MAX( 
        MAX(Table1[Date4]), MAX(Table1[Date5])
    )
    )
    )
    )​

    I am attaching my file as well for you to have a look.

    Hope this resolves your issue.

    Thank you,

    P.S. - All the max dates values for each row are coming from the Date 5 column itself, maybe you want to have a look into that. Just an observation.

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Attachment(s)

    pbix
    Max Date.pbix   120 KB 1 version


  • 3.  RE: Need Help finding Max date in a row

    Posted Feb 18, 2019 11:58 AM
    Thanks Vishesh... Is there a way i can create a column to find the days in between the Max date and the date1.

    ------------------------------
    Kaylan G
    Manufacturing
    8302217691
    ------------------------------



  • 4.  RE: Need Help finding Max date in a row

    Top Contributor
    Posted Feb 19, 2019 02:54 AM
    Hi @Kaylan G,

    You can use the following code for a Calculated Column:

    Difference =
    DATEDIFF(Table1[Date1],[Max Date],DAY)

    This should give you the difference between Max Date and Date1.
    Since the results of your Max Date are the same as Date5, you can find the difference between Date1 and Date5 in order to check the results of the new Calculated Column.

    I hope this resolves your issue.

    Thank you​,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 5.  RE: Need Help finding Max date in a row

    Posted Feb 19, 2019 07:32 AM
    Awesome... Thank you.

    Appreciate your help

    ------------------------------
    Kaylan G
    Manufacturing
    8302217691
    ------------------------------