Forum Discussion
Moving Average on filtered data
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])
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
)
)
Hi GilbertQ,
It produces an error: Datesbetween and Datesinperiod functions are only accpeting date column reference as a first arguement.
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])