Creating Groups of age ranges based on Age calculated from DOB

[NigelS]

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

[v-huizhn-msft]

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]

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

[RH-BIP]

Hey! i have the same "problem"...did you fix it?

[RH-BIP]

I found the solution I needed in this video --> https://www.youtube.com/watch?v=5_KDJGPjMnI

[Rfranca]

@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

[Rfranca]

@NigelS

see the link below

http://community.powerbi.com/t5/Desktop/Display-the-Age-range-using-Power-BI-Standpoint-Online/m-p/2...

[NigelS]

@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.