Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good Day,
Really need help in finding out how to get the moving average of previous months after filtering for a single month. I have come across some solutions in the forums but they do not seem to be working in my case for some reason (e.g., I cannot seems to clear the filter implemented by the slicer).
I am try to achieve the following, based on the data below (the data has been simplified and replaced with dummy data)
Current, the formulas I have tried to achieve this with are:
MA of Prev 3 months = CALCULATE(AVERAGE('Website Data'[Website Users]),DATESINPERIOD('Website Data'[StartOfMonthDate],DATEADD(LASTDATE('Website Data'[StartOfMonthDate]),-1,MONTH),-3,MONTH))
(This failed as soon as I filtered for that one month value)
I also tried an alternative solution from http://community.powerbi.com/t5/Desktop/Sum-year-and-previous-year-from-that-year-selected-from-filt... but I think I may be doing something wrong
MaxYearMonth = MAX('Website Data'[Month of Year])
MA of Prev 3 month v2 = CALCULATE(SUM('Website Data'[Website Users]), FILTER('Website Data', 'Website Data'[Month of Year]=[MaxYearMonth]-1 || 'Website Data'[Month of Year]=[MaxYearMonth]-2 || 'Website Data'[Month of Year]=[MaxYearMonth]-3))
The (failed) results are seen below:
I would be grateful if anyone could see how I could implement this. It has been driving me nuts for the past week or 2. Here is the work file for your convenience: https://drive.google.com/file/d/0BymvfSPfXzNFRC1HRkNBUjdZbFU/view?usp=sharing
**Updated: I have updated the broken link to the file above
Solved! Go to Solution.
Hi @OOneWork,
You can refer to below formula to achieve your requirement.
Meaures:
Select Month = IF(HASONEVALUE(Sales[Month]),VALUES(Sales[Month]),BLANK())
Previous Three Month Average =
var temp=[Select Month]
Return
AVERAGEX(FILTER(ALL(Sales),Sales[Month]>=temp-3&&Sales[Month]<temp),[Amount])
Table:
Create visuals.
Result:
Regards,
Xiaoxin Sheng
Hi @OOneWork,
You can refer to below formula to achieve your requirement.
Meaures:
Select Month = IF(HASONEVALUE(Sales[Month]),VALUES(Sales[Month]),BLANK())
Previous Three Month Average =
var temp=[Select Month]
Return
AVERAGEX(FILTER(ALL(Sales),Sales[Month]>=temp-3&&Sales[Month]<temp),[Amount])
Table:
Create visuals.
Result:
Regards,
Xiaoxin Sheng
Hi,
I have created the quick measure to calculate the moving average with dynamic slicer. But facing column total error for the measure. Like it has to sum Jan to Dec data and show it in the column but column total only taking Dec month data everytime.
Please help me with the following issue
I am getting column total error and taking Matrix table
Rows - Country,ProductCategory
Column - Month
I'm getting column total incorrect for Forecast(Blue) data.
Dax Formula (Quick Measure) - Product_Count rolling average =
IF(
ISFILTERED('Table_name'[dimdate]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Table_name'[dimdate].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Table_name'[dimdate].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, 'Moving Average'[Moving Average Value], MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Table_name'),
'Table_name'[dimdate].[Year],
'Table_name'[dimdate].[QuarterNo],
'Table_name'[dimdate].[Quarter],
'Table_name'[dimdate].[MonthNo],
'Table_name'[dimdate].[Month]
),
__DATE_PERIOD
),
CALCULATE(
SUM('Table_name'[Product_Count]),
ALL('Table_name'[dimdate].[Day])
)
)
)
Thanks
Thank you XiaoXin, it works well!! The intermediate variable defined in the formula coupled with AVERAGEX and FILTER(ALL()) did the trick.
Is there an example using CALCULATE though by any chance though? Where I can use DATEPERIOD to define the date range? The current month solution will work within the year (2016), but once I extend it to 2015, I run into problems.
Cheers
Hi @OOneWork,
You can try to use below formula if it fix the issue:
Previous Three Month Average =
var temp=[Select Month]
var currYear=MAX(Sales[Year])
Return
if(temp>=4,
AVERAGEX(FILTER(ALL(Sales),Sales[Month]>=temp-3&&Sales[Month]<temp),[Amount]),
AVERAGEX(FILTER(ALL(Sales),Sales[StartDate]>=DATE(currYear-1,12-(3-temp),1)&&Sales[StartDate]<DATE(currYear,temp,1)),[Amount]))
Regards,
Xiaoxin Sheng
Hi Xiaoxin,
Just to follow up on your solution, is there a special workaround for data with multiple rows? E.g., there is a 3rd dimension like "Country" that is causing 1 month to have multiple records.
I tried to use GROUPBY/SUMMARIZE to return a aggregated table, but I am having trouble referencing it from filter. Do you happen to know how to do this? The formula I used below is not working 😞
Previous Three Month Average =
var temp=[Select Month]
Return
AVERAGEX(FILTER(GROUPBY(ALL(Sales),Sales[Country],"Total Amount",[Amount]),Sales[Month]>=temp-3&&Sales[Month]<temp),[Amount])
Thank you again for your suggestions.
Can you please upload the file once again. Link is broken.
Try the blog posted on this HERE. This will give you some more guidance.
Thank you for the link. It was one of the references I used too. It did not work for me (or I implemented it wrongly) though.
Cheers
Good Day,
Really need help in finding out how to get the moving average of previous months after filtering for a single month. I have come across some solutions in the forums but they do not seem to be working in my case for some reason (e.g., I cannot seems to clear the filter implemented by the slicer).
I am try to achieve the following, based on the data below (the data has been simplified and replaced with dummy data)
Current, the formulas I have tried to achieve this with are:
MA of Prev 3 months = CALCULATE(AVERAGE('Website Data'[Website Users]),DATESINPERIOD('Website Data'[StartOfMonthDate],DATEADD(LASTDATE('Website Data'[StartOfMonthDate]),-1,MONTH),-3,MONTH))
(This failed as soon as I filtered for that one month value)
I also tried an alternative solution from http://community.powerbi.com/t5/Desktop/Sum-year-and-previous-year-from-that-year-selected-from-filt... but I think I may be doing something wrong
MaxYearMonth = MAX('Website Data'[Month of Year])
MA of Prev 3 month v2 = CALCULATE(SUM('Website Data'[Website Users]), FILTER('Website Data', 'Website Data'[Month of Year]=[MaxYearMonth]-1 || 'Website Data'[Month of Year]=[MaxYearMonth]-2 || 'Website Data'[Month of Year]=[MaxYearMonth]-3))
The (failed) results are seen below:
I would be grateful if anyone could see how I could implement this. It has been driving me nuts for the past week or 2. Here is the work file for your convenience: https://drive.google.com/open?id=0BymvfSPfXzNFRC1HRkNBUjdZbFU
Cheers,
Joe
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |