Forum Discussion

twister8889's avatar
twister8889
Icon for Helper V rankHelper V
6 years ago
Solved

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...
Need Help
  • Fowmy's avatar
    Fowmy
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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!