Forum Discussion
Age Calculation in Data Transformation via a Custom Column
Good night.
I'm new to power bi and I need to do a job to finish a course.
To this end, I have been provided with a database in which the date of birth appears only in "year" and in numerical format. I want to add two columns as a custom column in "Data Transformation"
1. Age:
2. Age Group
But I don't know how to do it, if anyone can help me I would appreciate it.
Screenshot attached.
Thanks a lot
Hi,
Since the year of birth is provided instead of the date of birth, I am assuming that to calculate age, you just need to subtract the year of birth from the current year. This can be done by using the M query formula below -
Age in Years = Date.Year(DateTimeZone.UtcNow()) - [Year of Birth]
I hope it's the result you are looking for in the Age field.
Age Group: There are various ways to create age groups/ bins in DAX and M Query. Since no requirements have been mentioned, I am assuming you need to create simple age groups to classify the ages. This can be done using the conditional column in the query editor as shown below -
- Go to Add Column tab and select Conditional Column
- Enter the grouping logic on the Age in Years field as shown below -
You can write the conditions as per your requirements for grouping the ages into bins.
Let me know if it helps!
3 Replies
Hi,
Since the year of birth is provided instead of the date of birth, I am assuming that to calculate age, you just need to subtract the year of birth from the current year. This can be done by using the M query formula below -
Age in Years = Date.Year(DateTimeZone.UtcNow()) - [Year of Birth]
I hope it's the result you are looking for in the Age field.
Age Group: There are various ways to create age groups/ bins in DAX and M Query. Since no requirements have been mentioned, I am assuming you need to create simple age groups to classify the ages. This can be done using the conditional column in the query editor as shown below -
- Go to Add Column tab and select Conditional Column
- Enter the grouping logic on the Age in Years field as shown below -
You can write the conditions as per your requirements for grouping the ages into bins.
Let me know if it helps!
Good night.
Thank you very much for your help and your indications, it has come out just as you said.
Thanks a lot
thanku
