I am a power user of Power BI, but a relative novice to DAX and have run across an issue creating a cohort analysis that I cannot solve with just queries and pulling data from multiple tables.
I have two tables, one for Go Lives and another with Health scores. Both tables have similar data which I use to form a Master Key through an additional column (Region,Customer,Renewal Date). When a customer goes Live, the Year/Month that they Go Live forms a cohort (ex 2021-1 for all January Go Lives). On the other side, once a customer signs a contract, they get a monthly Health Score (ex. same customer started a contract in October 2020 so receive a monthly health score starting in Nov 2020).
For the cohort analysis, I want to take the Health Scores from the month AFTER their Go Live and start to track it numerically (Month 1, Month 2, etc for a minimum of 2 years).
What I can't figure out is how to get calendar date months, compare them to a Go Live date and then insert them into a numeric monthly sequence so I can use the Matrix Table in Power BI to create a proper Impact Plot with Avg. Health Score as the value in the matrix table.
I've read articles using DAX to get the values into another format, but it is usually from one table and gets above my knowledge on how to reproduce. Any help is greatly appreciated.
Cohort sample data_2 tabs
