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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
LijunChen
Helper II
Helper II

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.

LijunChen_0-1687184764011.png

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. 

  

2 REPLIES 2
amitchandak
Super User
Super User

@LijunChen , Are looking for

Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Anal...

 

 

Or age bucketing

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

 

and then use a bucket table

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

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

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.