Cohort Analysis Help

[lkhall]

I have been struggling to determine how to calculate the denominator values in the attached examples. Getting the Cohort data and Numerators are easy but just cant figure out the denominator to get the right percentages. Any help would be greatly appreciated. In the end I am trying to replicated the percentages matrix. 

[AnthonyGenovese]

Where does the denominator come from?

[lkhall]

The denominator is a calculation of a formula 

SUM(OFFSET($D$105,-$D128,0):OFFSET($D$105,-MAX($D129,D$130)-1,0)) = 2023 month 0 cohort value 628

[AnthonyGenovese]

You need to be more clear with what data you have already loaded and what the business logic is. Please provide a table of what you are loading into power BI, the business logic (not excel), and then an example.

[lkhall]

I don't see anyway to attach a file to provide you the raw data. 

[AnthonyGenovese]

Just provide an example using the table feature in the post reply

[lkhall]

132	37	29	21	28	25	23	23	22	15
399	135	93	70	68	61	59	60	55	49
393	184	138	120	98	91	83	86	80	66
296	142	129	111	100	91	87	77	80	76
177	108	94	83	80	70	70	64	65	62
263	132	123	123	108	100	92	90	85	90
400	271	237	226	220	217	190	196	181	173
779	558	503	477	448	423	421	363	296	259
628	344	264	169	102	45	0	0	0	0

[lkhall]

This is an example of  the data that I am using and represents the numerator part of the equation. I am not that familiar with the OFFSET function in Excel and having a nested OFFSET inside an offset is thowing me off on how the denominator is being calculated. 

[lkhall]

132	132	132	132	132	132	132	132	132	132	132
399	399	399	399	399	399	399	399	399	399	399
393	393	393	393	393	393	393	393	393	393	393
296	296	296	296	296	296	296	296	296	296	296
177	177	177	177	177	177	177	177	177	177	177
263	263	263	263	263	263	263	263	263	263	263
400	400	400	400	400	400	400	400	400	400	400
779	779	779	779	779	779	779	695	624	546	466
628	517	398	279	154	78	162	233	311	391	446

[lkhall]

This is the expected outcome for the denominator