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.
@Anonymous , Are looking for
Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Analysis/ba-p/1393410
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/1387187#M626
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.
