Average of Last 4 sprints

[Syndicate_Admin]

I have a Table in Power BI for which i want to calculate Average of last 4 sprints.

Team	Sprint	Story Points
A	7	5
A	8	6
A	9	7
A	10	5
A	11	5
A	12	4

I was looking for Result like this. How do i write a measure to get this result

Team	Sprint	Story Points	Average
A	7	5	0
A	8	6	0
A	9	7	0
A	10	5	0
A	11	8	5.75
A	12	4	6.5

[Syndicate_Admin]

Hi @JeevanMallya 

You've asked for a Measure but you only get that with DAX.  And you've posted in the Power Query forum which doesn't use DAX, PQ uses the M language. 

So are you after a DAX Measure or a PQ solution?

Also, is there only ever 1 team?  Or do you need the average of the last 4 sprints for every team listed?

Phil

[Syndicate_Admin]

Thank you for reply.

Yes there are Multiple Teams for which Average velocity needs to be calculated. I just showed one but the real Table looks like

Team Name	Sprint Name	Total Story Points
Team 1	Sprint 1	5
Team 1	Sprint 2	25
Team 1	Sprint 3	10
Team 1	Sprint 4	15
Team 1	Sprint 5	25
Team 1	Sprint 6	30
Team 1	Sprint 7	18
Team 2	Sprint 1	17
Team 2	Sprint 2	20
Team 2	Sprint 3	35
Team 2	Sprint 4	25
Team 2	Sprint 5	30
Team 2	Sprint 6	25
Team 2	Sprint 7	20
Team 2	Sprint 8	27
Team 2	Sprint 9	33

I am looking for Rolling Average of Last 4 sprints.

I am after a DAX query.

[Syndicate_Admin]

You might have already found https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/ but that's based on months. My 2 cents would be to maybe add a fictive date to make the calculation. If you succeed in adding a date column the calculation should be pretty doable if you follow the blogpost.

[Syndicate_Admin]

Hi @JeevanMallya ,

Please try this measure:

Average = 
IF (
    MAX ( 'Table'[Sprint Number] ) > 4,
    AVERAGEX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Team Name] = MAX ( 'Table'[Team Name] )
                && 'Table'[Sprint Number] <= MAX ( 'Table'[Sprint Number] )
                && 'Table'[Sprint Number]
                    > MAX ( 'Table'[Sprint Number] ) - 4
        ),
        [Total Story Points]
    ),
    0
)

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Syndicate_Admin]

Thank you very much