Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Age calculations using Date columns

Hi,

 

I have a couple of age calculations to be done based on the below formulae:

 

Age 1 = Round((Date1 ‐ Date(Date2, 1, 1)) x 12 / 365,0) + 1

Age 2 = Round((Date1 ‐ Date(Date3, 1, 1)) x 12 / 365,0) + 1

 

Date1 is of Data Type Date/Time whereas Date2 and Date3 are of Data Type Date with YYYY format.

 

Does the Data Type for Date1 also need to be Date with Date with YYYY format?

 

I wanted to confirm so that I can verify the results correctly.

 

Please help with these date calculations so that the DAX functions fetch the correct results.

 

Thanks,

Vishy

 

 

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi Anonymous ,

    From your description, it seems that your formula should not be right.

    Actually, I have a little confused about your requirement.

    Assuming that you have the sample data below.

    Do you want to get the output like this based on your logic?

    If it is your case, please create the calculated column with the formula below.

    age1 = ROUND((YEAR('Table1'[Date1])-YEAR('Table1'[Date2]))*12/365,0)+1

    In addition, if your desired age1 is like below, you could try this formula.

    Column = ROUND(('Table1'[Date1]-'Table1'[Date2])*12/365,0)+1

    If you still need help, please share your desired output so that we could help further on it.

    Best  Regards,

    Cherry

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Cherry,

       

      The formulae is basically converting the difference in two dates into months.

       

      In your explanation below, I guess it would be the second one where you have shown the formulae for calculated Column. Please correct me if I am wrong.

       

      Since it's basically a formulae to convert difference in two dates into months, please let me know if there is an even simpler or efficient way of accomplishing the same.

       

      Thank you for your help.

       

      Regards,

      Vishy

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I am facing another issue in this. 

         

        I created a calculated Column = 

        Age1 = (YEAR(Date1)-Date2)*12 + MONTH(Date1)
        I felt this is a simpler way to convert the difference in dates into months.
         
        Date1 is of Data Type Date/Time and Date2 is of Data Type Date with Format YYYY.
         
        For testing, when I create a table and drag Date1, Date2 and Age1 into the table, I do not see any results. It's a blank table that I get despite there being data imported for the same.
         
        PS: When I drag only Date1 into the table, I get data in the date hierarchial format but when I add Date2, it does nto show any data. 
         
        Is there something to do with the data model relationships? I created a Merged dataset after joining base table to 3 more intermediate tables.
         
        Regards,
        Vishy