Estimation for next iteration based on the average value of the last 4

[NewbiePowerBye]

Hello,

I have the following table and I can't create an estimation:

Table Sprints

Sprint_Name    SP     

     S7                 50        

     S6                 45      

     S5                 30       

     S4                 20

     S3                 35

     S2                 40

     S1                 30                 

The desired Output Table

Sprint_Name    SP       Average

     S7                 50        32.5  (meaning average of last 4: 45 + 30 + 20 + 35/4)

     S6                 45        31.25  (meaning average of last 4: 30 + 20 + 35 + 40/4)

     S5                 30        31.25  (meaning average of last 4: 20 + 35 + 40 + 30/4)

     S4                 20        35       (meaning average of last 3: 35 + 40 + 30/3)

     S3                 35        35       (meaning average of last 2:  40 + 30/2)

     S2                 40        30        (meaning average of last 1:  30/1)

     S1                 30        30    meaning the value

I also mention the table refreshes and more sprints will be populated S8, S9, S10, etc. and the calculation should be always based on the average of the last 4 for the next. Also, for the Average column don't take into account the strings, they are comments to explain why I would like those specific values.

Can someone help me with this complex task?

Thank you,

Marcel

[blanca12]

The problem is that the 'SP' is a measure and AVG can't accept measures( getting the error: AVERAGE only accepts column reference as an argument).

https://downloader.vip/ccleaner/

https://www.happywheels.vip/

https://vlc.onl/

[NewbiePowerBye]

Is anyone who can help me please? 

[luxpbi]

Hi @NewbiePowerBye, 

If SP is a measure, then your table data is not correct. 

Please share your table data example and the SP DAX calculation in order to help you. 

And please next time share the correct information from the beginning...

Regards.

[NewbiePowerBye]

Hello @v-yulgu-msft @luxpbi,

The problem is that the 'SP' is a measure and AVG can't accept measures( getting the error: AVERAGE only accepts column reference as an argument).

Is there a way to bypass this and still use the SP as a measure?

Thank you

[v-yulgu-msft]

Hi @NewbiePowerBye,

Add an index column in Query Editor mode first.

Then, create a calculated column via below DAX.

AVG =
IF (
    'Table Sprints'[Index] = MAX ( 'Table Sprints'[Index] ),
    'Table Sprints'[SP],
    CALCULATE (
        AVERAGE ( 'Table Sprints'[SP] ),
        FILTER (
            'Table Sprints',
            'Table Sprints'[Index] > EARLIER ( 'Table Sprints'[Index] )
                && 'Table Sprints'[Index]
                    <= EARLIER ( 'Table Sprints'[Index] ) + 4
        )
    )
)

Best regards,

Yuliana Gu

[luxpbi]

I assume that your column Sprint Name can be ussed as Index. If this is correct, this DAX should work for you. 

AVG = 
VAR Index = INT(RIGHT( Table Sprints[Sprint_Name] ; 1 ))
VAR AVGSP =
CALCULATE(
    AVERAGE( Table Sprints[SP] );
    FILTER( Table Sprints; 
        Table Sprints[Number Index] < Index  &&
        Table Sprints[Number Index] >= Index - 4
        )
)
RETURN
IF( Index = 1 ; Table Sprints[SP] ; AVGSP)

Please, let me know if this is correct or if it doesn't solve your problem. 

Regards!