Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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])))
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.
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])))
@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.
@ankitpatira , I have already joined the dates in sales table and calendar table.... Do you have any other idea?
Thank you.
@ankitpatira , yes.. I used pivottable to get the unique dates from Sales table to prepare Calendar table.
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/
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.