Moving average by month / moving variance

[twister8889]

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.

[Fowmy]

@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

[twister8889]

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

[Fowmy]

@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

[twister8889]

OK, please check is more clear for you..

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

[harshnathani]

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)
Did I answer your question? Mark my post as a solution!

[Fowmy · 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

[twister8889]

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.