Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Moving average starts at zero

I have table where each row in the table has an associated date. I'm using the following measure to calculate a moving 30-day average. 

MyMovAvg = 

CALCULATE( [MyCount],DATESINPERIOD(MyTable[Date],LASTDATE(MyTable[Date]),-30,DAY) ) / 30
MyCount is another measure that just applies COUNTA to MyTable. I also have a Slicer that lets me select a date range.
The problem is that the curve begins at zero and rises for the first 30 days that I'm visualizing. After those first 30 datapoints, I see the values that I expect.

How can I change this calculation so that I don't get this "drop" in my first 30 data points? Thanks in advance.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Need to set some boundries using variables in your measure. 

    30 Day MA:=
//Finds the first sales date
VAR __FirstSalesDate= 
CALCULATE(
    FIRSTDATE(Table1[Date]), 
    ALL( Table1)
)

//Set the length of the moving average
VAR __MovingAvgLength= 30

//Add the moving average length to the first sales date as when to start 
VAR __FirstOffset = __FirstSalesDate + ( __MovingAvgLength -1 )

//Actual Moving average Function
VAR __MovingAvg =
CALCULATE(
    [Avg Sales], //Separate Measure
    DATESINPERIOD( 
        'Calendar'[Date],
        MAX('Calendar'[Date]),
        ( ABS(__MovingAvgLength)*-1), //Takes the absolute value and makes it negative
        DAY
    )
  )

Return
IF( 
    MAX( 'Calendar'[Date]) >= __FirstOffset,
     __MovingAvg
)

     

    This assumes you have a dedicated Calendar table and it is marked as a Date Table since it uses DATESINPERIOD.