Dynamic Moving Average per Day based on multiple values

[Josh_M]

Hi All,

I am trying to achieve a Moving Average per Day with a dynamic adjustment. My issue is that I have multiple values per day and seem to be struggling with that. I have already made a moving average based on individual values but I also need the one based on Day.

Currently what I have is a table ('TBUData') like this:

The moving Average works when its individual days but when I vary it to more than one it doesn't seem to be doing the right calculation (4 Day set to parameter):

My Measure calculation is this:

Moving Avg Per Day = 
//Selecting the date in the range
VAR _LastDate =
MAX (TBUData[Date])
//Defining the duration to be considered for average calculation(k)
VAR _Duration = [Parameter By Day]
//Filtering the Calendar Table for the defined range
VAR _CalculationPeriod =
FILTER (
ALL ( TBUData),
AND (
TBUData[Date] > _LastDate - _Duration, 
TBUData[Date] <= _LastDate 
)
)
//Calculating the Moving Average
VAR _MovingAverage =
IF (
COUNTROWS ( _CalculationPeriod ) >= _Duration, 
CALCULATE (
AVERAGEX(TBUData,[M2Average]), 
_CalculationPeriod
)
)
RETURN
_MovingAverage

For relationships I also have master date table although I am just trying to get it to work with the current table for now:

Any help would be appreciated 

[amitchandak]

@Josh_M , Try a measure like with help from date table

4 Day Avg = CALCULATE(AverageX(Values('Date'[Date]),calculate(Sum('Table'[Value])))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-4,DAY))

[Josh_M]

Hi @amitchandak, I have tried it like that few times but it just shows blank even though I know the relationship works through other plots