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

[LijunChen]

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. 

[amitchandak]

@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

[LijunChen]

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.