Moving Average on filtered data

[spoony]

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. 

[GilbertQ]

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]

Hi @GilbertQ,

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

[GilbertQ · Accepted solution]

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

[spoony]

Hi @GilbertQ, ya it needs to be on a graph and matrix. I'll test it out anyway.