cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
twister8889
Helper V
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.

 

moving avg.png

1 ACCEPTED SOLUTION

@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 🙂

YouTube, LinkedIn

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

7 REPLIES 7
Fowmy
Super User
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?
How to get your questions answered quickly?

_____________________________________
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 🙂

YouTube, LinkedIn

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

@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 🙂

YouTube, LinkedIn

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

OK, please check is more clear for you..

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

 

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
    )
)

 

 

1.jpg

 

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

@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 🙂

YouTube, LinkedIn

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.