Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GiangLe
Helper I
Helper I

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.

 

Untitled.png

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

2 ACCEPTED SOLUTIONS

Hi GiangLe

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

6.png

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

9.png

finally,you will get the correct results.

8.png

 

7.png

 

View solution in original post

tringuyenminh92
Memorable Member
Memorable Member

Hi my friend,

 

For your case, i have created one topic to describe how i achieved the dynamic age grouping. Please try check it and let me know if it could be fit your requirement or not.

https://tringuyenminh92.com/index.php/2018/03/30/creating-dynamic-age-as-dimension-field-for-age-gro...

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

 

View solution in original post

4 REPLIES 4
tringuyenminh92
Memorable Member
Memorable Member

Hi my friend,

 

For your case, i have created one topic to describe how i achieved the dynamic age grouping. Please try check it and let me know if it could be fit your requirement or not.

https://tringuyenminh92.com/index.php/2018/03/30/creating-dynamic-age-as-dimension-field-for-age-gro...

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

 

v-juanli-msft
Community Support
Community Support

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 20171.png

 

 

22 years old in 2017 is actually 23 now2.png

 

 

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

Best regards

Maggie

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. 

Hi GiangLe

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

6.png

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

9.png

finally,you will get the correct results.

8.png

 

7.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.