Forum Discussion

GiangLe's avatar
GiangLe
Helper I
8 years ago
Solved

Calculate dynamic age

Hi everyone,

I'm facing this issue that actually have been posted once in this forum. However, it remained unsolved. So i'd like to bring it back. hopefully someone has the answer for it this time.

 

I have this dataset in which age is calculated based on date of birth and today. Hence this would provide correct result for this year timeframe. However, if i look back at data in the past for example, sales to 22 years old customer in 2017, this would give me wrong results. Because it's calculated based on customers who are 22 years old in 2018.
The one who was 22 years old in 2017 is actually 23 now and isnt taken into consideration.

I hope i've provided the problem clearly. Have a nice day everyone

Need Help

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    Hi GiangLe

    First, create a table with a column ‘year’ and apply it in the slicer.

    Then create a measure of the age based on the date of birth and the selected year.

    age =
DATEDIFF ( MAX ( Table1[dataofbirth] ), MAX ( Table2[year] ), YEAR )

    you will see information of 22 years old customer in 2017

     

     

    22 years old in 2017 is actually 23 now

     

     

    If you have any question, feel free to ask me.

    Best regards

    Maggie

    • GiangLe's avatar
      GiangLe
      Helper I
      8 years ago

      Hi Maggie, thank you for your reply. Even tho, the photo i posted earlier happens to have all customers were born on 1/1/YYYY. However, in my dataset, there are other days as well. Hence when i calculated age based on year only would leave out some anomalies. 

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        8 years ago

        Hi GiangLe

        You can create the column ‘year’ in this form, by default, month and date are set as 1/1.

        then you need to change the format of 'year' field to 'Date Hierarchy'after you add this field to a slicer.

        finally,you will get the correct results.