Skip to main content
cancel
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.

Reply
LijunChen
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.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.