Forum Discussion

spoony's avatar
spoony
Helper I
9 years ago
Solved

Moving Average on filtered data

Hi, I got the moving average working as below:

 

 

MovingAverageCost = CALCULATE (
    AVERAGE ('Cost Table'[Cost]),
    DATESINPERIOD (
       'Cost Table'[Month],
        LASTDATE ( 'Cost Table'[Month] ),
        -3,
        MONTH
    )
)

 

But each time i take out certain months with a filter, i need the moving average to ignore those months taken out and calculate a new average based on the filtered months. Is there something i can add to the code to do that? To make it easier its always consecutive, like for [January, February, March, April], if i filter out [January, February],  [March]'s moving average should be equal to its average. 

 

 

 

Need Help
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    Hi spoony

     

    How do you want your data to be used, in a matrix or a Visual?

     

    Reason is that this would work below, but I am thinking it might not be exactly what you are looking to achieve.

     

    You already have got your Average Measure [Avg]

     

    Then you create a new measure which will count the months selected.

    Month Count = CALCULATE(
     DISTINCTCOUNT('Cost Table'[Month] ),
          ALLSELECTED('Cost Table'[Month] )
    )

    And then if you create this final measure it will then have the total correct, but if in a matrix each line will appear to be incorrect.

    Moving Average = DIVIDE([Avg],[Month Count])

     

4 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    Hi spoony

     

    This is a total guess, but it might work.

     

    What about if you modify your measure to the following:

     

    MovingAverageCost = CALCULATE (
    AVERAGE ('Cost Table'[Cost]),
    DATESINPERIOD (
       ALLSELECTED('Cost Table'[Month]),
        LASTDATE ( ALLSELECTED('Cost Table'[Month] )),
        -3,
        MONTH
    )
)
    • spoony's avatar
      spoony
      Helper I
      9 years ago

      Hi GilbertQ,

       

      It produces an error: Datesbetween and Datesinperiod functions are only accpeting date column reference as a first arguement.

      • GilbertQ's avatar
        GilbertQ
        Super User
        9 years ago

        Hi spoony

         

        How do you want your data to be used, in a matrix or a Visual?

         

        Reason is that this would work below, but I am thinking it might not be exactly what you are looking to achieve.

         

        You already have got your Average Measure [Avg]

         

        Then you create a new measure which will count the months selected.

        Month Count = CALCULATE(
     DISTINCTCOUNT('Cost Table'[Month] ),
          ALLSELECTED('Cost Table'[Month] )
    )

        And then if you create this final measure it will then have the total correct, but if in a matrix each line will appear to be incorrect.

        Moving Average = DIVIDE([Avg],[Month Count])