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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I
Resolver I

Creating Dynamic Age Group changing with Date and be used as Chart Legend or Matrix Table Header

Hello Forum Buddies,


I am trying to create an age group based on birth dates, and then use this as a legenda in a timeline chart/ or as a column in matrix table for a series of indicators/outcomes (created with dax measures) that trends with year/month. I have several data tables. 

The calendar date table:  the X-axis of the year/month values is base on.

The client demographic table:  the client's gender, race, and birth date; clients id are linked with the fact tables

The facts tables: including referrals records (dates etc), service records (start / end dates, etc). Indicator measures are based on these.


In the screenshot above, I would like to use age group as a legend in the chart and as a column head in the matrix, showing trends of indicators across time by a breakdown of age groups. It is pretty stragith forward to use age and race group for the purpose, as they are straightforward columns in the clients table. I can also use birth year for the purpose too. But creating a dynamic age group that changes with time (calendar year/month) seems elusive. I can create a dynamic age group that can be displayed for each client as each time point. But I don't know how to use the age group as a legend or matrix heading.

Any advice, suggestions are welcome. 


Super User
Super User

@LijunChen , Are looking for

Cohort Analysis:



Or age bucketing

Age = sumx(Table, datediff(Table[Date], max(Date[Date]), month)


and then use a bucket table

Dynamic Segmentation Bucketing Binning

Dynamic Segmentation, Bucketing or Binning:

Thanks, @amitchandak :

I like your blog on cohort analysis. It may be the best way to solve my problem. 

Can you share the data and Pbix file for the cohort analysis. I would like to go over it step by step to make sure I understand all the procedures.  

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.