Moving Average Formula ERROR

[InsureBI]

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.

[Sean]

@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
)

[InsureBI]

@Sean thanks, this works with one change.  Replace MAX with EARLIER.

Your help is much appreciated.

[Sean]

@InsureBI I read your post to mean you want a MovAvg => MEASURE So that's what I provided...

Apparently you want a Calculated COLUMN so go with @Vvelarde's formula

[Vvelarde]

MovAvg = CALCULATE(AVERAGE(Policy[Coverage]),FILTER(ALL(Policy),Policy[EFF_DATE] <= EARLIER(Policy[EFF_DATE])))

[InsureBI]

Thanks.

So you are suggesting that I drop the table reference to the calculated measure.  I did and got the same error message.