cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
desiree Frequent Visitor
Frequent Visitor

Need help - Moving Average Formula ERROR

 

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.

 

chart 3.png

 

Thank you!

7 REPLIES 7
ankitpatira Super Contributor
Super Contributor

Re: Need help - Moving Average Formula ERROR

@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 Frequent Visitor
Frequent Visitor

Re: Need help - Moving Average Formula ERROR

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

 

Thank you.

ankitpatira Super Contributor
Super Contributor

Re: Need help - Moving Average Formula ERROR

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

desiree Frequent Visitor
Frequent Visitor

Re: Need help - Moving Average Formula ERROR

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

kcantor Super Contributor
Super Contributor

Re: Need help - Moving Average Formula ERROR

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/



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




samdthompson Established Member
Established Member

Re: Need help - Moving Average Formula ERROR

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

 

 

samdthompson Established Member
Established Member

Re: Need help - Moving Average Formula ERROR

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 149 members 1,903 guests
Please welcome our newest community members: