cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NigelS Frequent Visitor
Frequent Visitor

Creating Groups of age ranges based on Age calculated from DOB

Hi,

 

I am quite new to PowerBI and hope someone can give me some ideas on the following...

I have a table of people with DatesOfBirth and need to display the number of people whose age was 0-5years, 6-10years, etc, at a selected period end date, e.g. 31/12/2017. From other posts here I found a method to allow the user input the end-date in a slicer and to dynamically calculate the Age as a measure. So my new measure now shows each persons age on the selected date.

However I can't find any way now to put those ages in a group or a bin, e.g. the 0-5 year-olds etc. It seems that you can't group a measure in this way.

 

I tried creating a calculated column simply equal to the Age measure and this looked promising, as you can group the values of a calculated column, but when I tried to save the group definitions the window was unresponsive to the OK button.

 

Any ideas?

Thanks.

Nigel

5 REPLIES 5
Rfranca Member
Member

Re: Creating Groups of age ranges based on Age calculated from DOB

NigelS Frequent Visitor
Frequent Visitor

Re: Creating Groups of age ranges based on Age calculated from DOB

@Rfranca

thanks for sharing this solution. I am going to test it further. The challenge I have is that I need to do this in two steps, i.e. first to calculate the age at a given date that is dynamically selected from a slicer (I do this calculation in a measure), and then to apply the grouping into age ranges in a calculated field (using your example). However I don't think PowerBI will support this 2-step approach dynamically, but perhaps I can embed both calculations in the measure. I'll test further.

Rfranca Member
Member

Re: Creating Groups of age ranges based on Age calculated from DOB

@NigelS

 

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

Best Regards,
Rfranca

v-huizhn-msft Super Contributor
Super Contributor

Re: Creating Groups of age ranges based on Age calculated from DOB

Hi @NigelS,

 

It's hard to reproduce your scenario without sample table, you'd better create sample table and list expected result, so that we can post detailed solution which meet your needs. You can create fake data if your data is private.

Best Regards,
Angelia

NigelS Frequent Visitor
Frequent Visitor

Re: Creating Groups of age ranges based on Age calculated from DOB

Hi @v-huizhn-msft,

 

Thanks for your response and sorry for the delayed reply.

I have created some sample data as shown below.

The data has 3 columns: ID, Name and Date of Birth (DOB).

Based on the date of birth I want to show hown many people fall in different age brackets on a specific date (period end), e.g. 31/12/17.

 

So I added a table called Calendar which has just one column with a line per date starting from 1/1/2016 and ending with 31/12/2018.

I then have a measure called "Age at PE" which is calculated as "Age at PE = floor(YEARFRAC(MAX('Sheet1'[DOB]), MAX(Calendar[Date])),1)". This provides the fourth column below. The Age at PE can be controlled by the period end date range slicer at the top of the report (snip below). So far so good, and the values in the Age at PE column update with the slicer control.

 

However the last step in which I have used your example to add a Age Category column does not update as the slicer is moved.

i.e. it doesn't appear possible to have a calculation which can be based on a measure and update dynamically.

The formula for the calculation is "Age category = if([Age at PE]>=15, "over 15", "under 15").

Similarly I tried to use groups to group the Age at PE, but you can't group a measure.

 

So I am stuck at this point and would be most grateful for any further ideas.

 

Thanks,

Nigel

 

Age categories.PNG

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 92 members 1,766 guests
Please welcome our newest community members: