Forum Discussion
Moving Average Formula ERROR
I have a table of policy coverage and effective date and I am trying to compute a daily moving average of premium. Here is the formula I constructed, utilizing answers to similar questions on this forum.
SumCov = SUM (Policy[Coverage])
MovAvg = CALCULATE(AVERAGE(Policy[SumCov]),FILTER(ALL(Policy),Policy[EFF_DATE] <= EARLIER(Policy[EFF_DATE])))
The (simplified) Policy table with the computed measures SumCov and MovAvg should look like this:
Coverage, Eff_Date,SumCov,MovAvg
100, 6/1/2016,100,100
200,6/2/2016,900,300
600,6/2/2016,900,300
100,6/3/2016,1000,250
The formula for the new measure (MovAvg) results in the following error:
"Failed to resolve name 'SumCov'. It is not a valid table, variable, or function name."
It looks like I can't use a new measure inside a function. If so, how should I go about computing the daily moving average.
Thank you.
5 Replies
MovAvg = CALCULATE(AVERAGE(Policy[Coverage]),FILTER(ALL(Policy),Policy[EFF_DATE] <= EARLIER(Policy[EFF_DATE])))
Thanks.
So you are suggesting that I drop the table reference to the calculated measure. I did and got the same error message.
InsureBI Try these MEASURES
SumCov MEASURE = CALCULATE ( SUM ( Policy[Coverage] ), FILTER ( ALL ( Policy ), Policy[Eff_Date] <= MAX ( Policy[Eff_Date] ) ) ) MovAvg MEASURE = DIVIDE ( [SumCov], CALCULATE ( COUNTROWS ( Policy ), FILTER ( ALL ( Policy ), Policy[Eff_Date] <= MAX ( Policy[Eff_Date] ) ) ), 0 )
