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

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
6 years ago

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

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    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

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

      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.

  • 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!