Dax to Calculate the blank values in a matrix cohort

[atif_shikoh]

Please help me with the dax to find the below :LifetimeValues 

I have a data cohort like below(PBIX sample file attached) : 

Date		0	1	2	3	4	5	6	7	8	9	10	11	12	13	14
01/01/2024		94%	62%	37%	32%	27%	25%	24%	24%	23%	22%	21%	21%	20%	20%	19%
01/02/2024		93%	60%	36%	32%	25%	24%	23%	22%	21%	20%	19%	19%	18%	18%
01/03/2024		94%	66%	29%	25%	20%	19%	18%	17%	16%	16%	15%	15%	15%
01/04/2024		94%	66%	44%	39%	32%	30%	29%	28%	27%	26%	26%	25%
01/05/2024		94%	77%	41%	28%	23%	23%	22%	21%	20%	20%	19%
01/06/2024		94%	53%	27%	24%	20%	19%	19%	18%	17%	17%
01/07/2024		93%	80%	58%	53%	45%	43%	42%	40%	39%
01/08/2024		94%	74%	53%	48%	40%	38%	36%	35%
01/09/2024		94%	68%	47%	42%	35%	33%	32%
01/10/2024		93%	69%	45%	40%	32%	31%
01/11/2024		94%	68%	42%	38%	31%
01/12/2024		96%	69%	41%	37%
01/01/2025		95%	68%	40%
01/02/2025		96%	70%
01/03/2025		97%

I wanted to forecast the blank values in the above cohort... with the following rules ..

first find the  average of column values , it is as below

Average of Columns

94%

68%

42%

37%

30%

29%

27%

26%

23%

20%

20%

20%

18%

19%

19%

then find the relative percentage point drop in above averages as below :

column 1 percentage drop is (68%/94%)-1 and so on ... 

Relative PP Drop

-28.12%

-38.56%

-11.96%

-18.09%

-5.02%

-4.82%

-5.93%

-8.61%

-13.98%

-0.08%

-1.03%

-10.55%

6.76%

2.08%

Final step to find the blank values : 

use this relative percentage drop with previous value of the same row, i.e. 

for column 1, for date 01/03/2025 it will be calculated as below 

97%+(-28.12) = 69%.......

Thanks in advance...if someone can help , i will be truly obliged... 

[v-hashadapu]

Hi @atif_shikoh , We are closing this thread as we haven't heard from you in a while, according to our follow-up policy. If you have any more questions, please start a new thread on the Microsoft Fabric Community Forum. We will be happy to assist you! Thank you for being part of the community!

[v-hashadapu]

Hi @atif_shikoh , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu · Accepted solution]

Hi , Thank you for reaching out to the Microsoft Community Forum.

Based on your description, I took some sample data and worked out the solution.

Please refer attached .pbix file for reference and share your thoughts.

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[v-hashadapu]

Hi @atif_shikoh , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @atif_shikoh , Thank you for reaching out to the Microsoft Community Forum.

We are unable to download and check your .pbix file but to do this, from the details you have provided, you should start by unpivoting your data if it's still in wide format, so you have one row per Date and CohortMonthIndex. Then, create a calculated table that computes the average retention for each month index across all cohorts, along with the relative drop between each month. This drop is calculated as the percentage change from the previous month's average and it's what you'll use to drive your forecasting logic.

Next, create a measure that checks whether the actual retention value is blank. If it is, the measure looks up the previous month’s retention for that same cohort and applies the corresponding average drop to forecast the missing value. If a real value exists, it simply returns that. You can use this new measure in visuals like a matrix to display both real and forecasted retention percentages.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[atif_shikoh]

Thanks a lot @rajendraongole1 , however in my data model CohortRaw[Retention] is a measure....  I tried to tweak it but unfortunately it didn't work... I have uploaded the PBIX, Can you pls have a look?

LifetimeRetention.Pbix 

[rajendraongole1]

Hi @atif_shikoh  - Your current data is wide format. First, unpivot the columns 0 through 14.

Select columns 0 to 14.

 also ,

Right-click > Unpivot Columns.

Rename:

Attribute → CohortMonthIndex

Value → Retention

create a calculated tables as below:

AvgPerMonth =
SUMMARIZE(
FILTER(CohortRaw, NOT ISBLANK(CohortRaw[Retention])),
CohortRaw[MonthIndex],
"AvgRetention", AVERAGE(CohortRaw[Retention])
)

In the AvgPerMonth table, create a new column

RelativeDrop =
VAR CurrentMonth = VALUE(AvgPerMonth[MonthIndex])
VAR CurrentAvg = AvgPerMonth[AvgRetention]
VAR PrevAvg =
CALCULATE(
MAX(AvgPerMonth[AvgRetention]),
FILTER(AvgPerMonth, VALUE(AvgPerMonth[MonthIndex]) = CurrentMonth - 1)
)
RETURN
IF(
NOT(ISBLANK(CurrentAvg)) && NOT(ISBLANK(PrevAvg)),
DIVIDE(CurrentAvg, PrevAvg) - 1,
BLANK()
)

and now for missing forcast, Add a new column 

ForecastedRetention =
VAR ThisMonth = VALUE(CohortRaw[MonthIndex])
VAR ThisDate = CohortRaw[Date]
VAR Actual = CohortRaw[Retention]

VAR PrevRetention =
CALCULATE(
MAX(CohortRaw[Retention]),
FILTER(
CohortRaw,
CohortRaw[Date] = ThisDate &&
VALUE(CohortRaw[MonthIndex]) = ThisMonth - 1
)
)

VAR RelativeDrop =
CALCULATE(
MAX(AvgPerMonth[RelativeDrop]),
FILTER(AvgPerMonth, VALUE(AvgPerMonth[MonthIndex]) = ThisMonth)
)

RETURN
IF(
ISBLANK(Actual) && NOT ISBLANK(PrevRetention) && NOT ISBLANK(RelativeDrop),
PrevRetention * (1 + RelativeDrop),
Actual
)

I hope this works. please check