cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## Moving average by month / moving variance

Hi guys,

I need to calculate the average considering that: The previous months of 12, has average by the quantity of month. So if I have 6 months, the average is replicated to the previous months, if I have 7,8,9,10,11 and 12 months I have the average replicated to the previous months. If it has 13 months, I need to calculate the moving average by 12 months (this is working).

So, considering the image, I need that AVG2 has the value 0.366333333 to index minors that 13.

1 ACCEPTED SOLUTION
Super User

@twister8889

Use this measure :

``````New Avg =
VAR IND = SELECTEDVALUE('Table'[Index])
VAR _12 =
CALCULATE(
AVERAGE('Table'[Value]),
'Table'[Index] <= 12,
ALLSELECTED('Table')
)

VAR _OVER12 =
CALCULATE(
AVERAGE('Table'[Value]),
'Table'[Index] <= IND && 'Table'[Index] > IND - 12
,ALLSELECTED('Table')
)

RETURN

IF( IND > 12,
_OVER12,
_12
)``````

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
7 REPLIES 7
Super User

@twister8889

Can you share some sample data in excel/csv format and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper V

First of all, thank you for your answer, I share my pbi (Page 2) file here: https://1drv.ms/u/s!AoDYwrtLrltJnxlh-8VaZdW7EZVg

Super User

@twister8889

Your explanation is no quite clear for me, can you share the expected result for each line, maybe on Excel?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper V

OK, please check is more clear for you..

https://1drv.ms/x/s!AoDYwrtLrltJn0KJ4HE5amJyVpK2?e=Xd02Mz

Community Champion

HI @twister8889 ,

You will need to create an index column.

You can create this in Power Query

Post this you can use this measure.

``````Moving_Average_12_Months =
IF (
MAX ( Table1[Index] ) > 12,
AVERAGEX (
DATESINPERIOD (
Table1[Date],
LASTDATE ( 'Table1'[Date] ),
-12,
MONTH
),
CALCULATE (
SUM ( Table1[Values] )
)
),
DIVIDE (
CALCULATE (
SUM ( Table1[Values] ),
FILTER (
ALL ( Table1 ),
Table1[Index] <= 12
)
),
12
)
)
``````

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Super User

@twister8889

Use this measure :

``````New Avg =
VAR IND = SELECTEDVALUE('Table'[Index])
VAR _12 =
CALCULATE(
AVERAGE('Table'[Value]),
'Table'[Index] <= 12,
ALLSELECTED('Table')
)

VAR _OVER12 =
CALCULATE(
AVERAGE('Table'[Value]),
'Table'[Index] <= IND && 'Table'[Index] > IND - 12
,ALLSELECTED('Table')
)

RETURN

IF( IND > 12,
_OVER12,
_12
)``````

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper V

I will test, but I think that it is the correct solution, thank you so much.

Let me try to test this solution until Monday to check as a solution ok? More one time, thank you so much.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors