Rolling Average of Sum with non date field

[Anonymous]

Hi There,

I'm struggling to get this moving average working. I have a data set that looks like this

Team	Story points	Sprint
PBI	3	215
BPI	5	215
BPI	3	216
PBI	7	216
etc	etc	etc

I need the rolling average over 4 sprints of the sum of story points completed each sprint. I tried the following measure but it is not returning an accurate value.

Velocity Rolling 4 = 
CALCULATE(
    AVERAGEX('Fact Delivery Data',SUM('Fact Delivery Data'[Story Points])),
    FILTER(ALL('Fact Delivery Data'),
    'Fact Delivery Data'[Sprint]>=MAX('Facts Delivery Data'[Sprint])-3 
    && 'Fact Delivery Data'[Sprint]<=(MAX('Fact Delivery Data'[Sprint])
    )
    )
)

 
Any help would be really appreciated, I'm super stuck with this one

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Please let me know if you'd like to below one:

var b = MAX( 'Fact Delivery Data'[Sprint] ) 
VAR a =
 b - 3
 
RETURN
 CALCULATE (
 AVERAGEX ( 'Fact Delivery Data', SUM ( 'Fact Delivery Data'[Story Points] ) ),
 FILTER (
 ALL ( 'Fact Delivery Data' ),
 'Fact Delivery Data'[Sprint] >= a
 && 'Fact Delivery Data'[Sprint] <= b
 )
 )

[v-diye-msft]

hi @Anonymous 

Sorry  I am a little confused about your requirement based on your measure, could you please disclose the expected results?

[Anonymous]

Hi @v-diye-msft ,

I'm after a 4 sprint (period) rolling average of story points.

For instance, the sum of all points for sprint 1 was 4000, sprint 2 3000, sprint 3 3500, sprint 4 3000. The expected result is the average across these 4 sprints

[amitchandak]

Refer

https://community.powerbi.com/t5/Desktop/highest-value-by-category/td-p/428758

https://community.powerbi.com/t5/Desktop/Calculate-Average-per-category/td-p/362637