measure to calculate first value in matrix for another measure and applying a reduction to each week

[Elizz]

Hi

Not sure how to explain what i need help with seems impossible... and i hope ive used the right forum 😉

im replicating a spreadsheet in powerbi, 

i have a table of dates (weekending)

a measure calculating the waitlist cohorts:

Sum52Cohort = if(ISBLANK(CALCULATE(sum(Fact_PTL[Total]),

filter(Fact_PTL,Fact_PTL[DSU_Stage]="New OP"  &&

Fact_PTL[52WeekCohort]="52+ Cohort"))),0,CALCULATE(sum(Fact_PTL[Total]),

filter(Fact_PTL,Fact_PTL[DSU_Stage]="New OP"  &&

Fact_PTL[52WeekCohort]="52+ Cohort")))

i need to add a linear reduction, this has been done by taking the first waitlist cohort figure, and minusing a reduction rate.

which i have done in a measure: (used the static starting figue as i didnt know how to get the first using DAX)

Reduction = 46862/(int(max(Dim_Date[WeekEndDate])-min(Dim_Date[WeekEndDate]))/7)

this then gives the next weeks figure, then again using this new figure minusing the reduction rate again and so forth to the end of the period to get to zero.

example; 

Appreciate any help :):):)

thanks 

Liz

[Elizz · Accepted solution]

Hi

Ive manage to resolve this, through a few measures

thank you though.