Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
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.
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
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
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.
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |