cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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

6 REPLIES 6
New Member

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://www.happywheels.vip/

https://vlc.onl/

Helper I

Is anyone who can help me please?

Helper V

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

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

Regards.

Helper I

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

Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper V

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.