Need help - Moving Average Formula ERROR

[desiree]

Hello,

I am new to Power BI and DAX. I am trying to create a measure for calculating the moving average of 3 monthly sales by area on Power BI Desktop/Service. I have googled many websites but cannot find out what's problem in my DAX formula below..

On the dashboard there is a slicer for area, and also a timeline slicer for date, so the user can analyze the dashboard data for a month or quarter. I want to add a moving average line in the sales bar chart by months. 

I tried below DAX formula:

[3 Month Moving Sum Sales] =
CALCULATE([Sales],
          DATESINPERIOD(Calendar[Date],
                        LASTDATE(Calendar[Date]),-3, Month 
         ) / 3

However, it always displays the same value regradless which time period I select in timeline slicer. It shows the average of latest 3 months data in my data table rather the latest 3 months data I selected in slicer.

In my calendar table, I only include the date exist in data table (e.g. 1-jan-2015, 1-feb-2015, etc..) instead of all consquence dates (1-jan-2015, 2-jan-2015, ...)

Below are my data table.

Thank you!

[samdthompson]

Oh man I also see your data has 'na' on it. I bet thats a text column. try making another column with value([sales]) or alternately re import the table with the field formatted as number not text.

[samdthompson · Accepted solution]

HI, just did one of these this morning for a 6 wk rolling average $/kg:  

calculate(sum(Data[Operating]),DATESBETWEEN(Data[Week start],LASTDATE(Data[Week start])-42,FIRSTDATE(Data[Week start])))

/

calculate(sum(Data[kg]),DATESBETWEEN(Data[Week start],LASTDATE(Data[Week start])-42,FIRSTDATE(Data[Week start])))

[ankitpatira]

@desiree in power bi desktop, relationship view check that relation exists between two tables ie calendar table date column and date column in your sales table and for that relationship to exist you need dates in sales table for the dates in calendar table.

[desiree]

@ankitpatira , I have already joined the dates in sales table and calendar table.... Do you have any other idea?

Thank you.

[ankitpatira]

@desiree And dates in calendar table matches with dates in sales table ?

[desiree]

@ankitpatira , yes.. I used pivottable to get the unique dates from Sales table to prepare Calendar table.

[kcantor]

I had the same issue. I used this blog post and made changes to my DAX. It works perfectly. I do not remember what caused my issues but here is the link to the post:

http://www.powerpivotpro.com/2013/07/moving-averages-sums-etc/