Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
OOneWork
Frequent Visitor

Getting moving average for previous records after filtering for single value/row

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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:

Capture.PNG

 

Create visuals.

Capture2.PNG

 

Result:

Capture3.PNGCapture4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

8 REPLIES 8
v-shex-msft
Community Support
Community Support

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:

Capture.PNG

 

Create visuals.

Capture2.PNG

 

Result:

Capture3.PNGCapture4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

anushaghi123_0-1694520310026.png

 

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

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

BhaveshPatel
Community Champion
Community Champion

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.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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

OOneWork
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.