Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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 )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
100 | |
85 | |
77 | |
65 |
User | Count |
---|---|
120 | |
111 | |
95 | |
83 | |
75 |