cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Rolling average last 3 months

Hi,
I hope you can help me with a shallange:
I have a Power BI report, where I need to make a measure (I don't have access to the source table) which calculates the rolling average for the last 3 months.
And the rolling average shall be made on the sum per month (not rolling on the date value).
Example:
in the table below, I have the "Daily Sales-Sum per month" in 2019. The datasource is one table (not a datamodel with dimensions and facts), where leaf-level is transactions by date.
I need the rolling average on the monthly sum-values.

monthTable.PNG

I have tried to make the measure (please see below), but the result is wrong(table above).

 

DAX-Script:

--------------------------------------
Rolling Average 3 months =
VAR LastDate_ = LASTDATE(Table[Calendar Day])
RETURN
AVERAGEX(
DATESINPERIOD(
'Table'[Calendar Day];
LastDate_; -3; MONTH);
SUMX(
KEEPFILTERS(VALUES('Table'[Month]));
CALCULATE(SUM('Table'[Sales]))
)
)
---------------------------------------------

 

the result I need is this (here shown for the last two months, as an example):

monthTable2.PNG

 

Any ideas? All inputs will be greatly appreciated.

Thanks.

Br,

JayJay

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Responsive Resident
Responsive Resident

Re: Rolling average last 3 months

How about this?

 

Rolling Average 3 months =
VAR LastDate_ =
    LASTDATE ( Table[Calendar Day] )
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( 'Table'[Month] ); CALCULATE ( SUM ( 'Table'[Sales] ) ) );
        FILTER (
            ALL ( Table );
            [Calendar Day] <= LastDate_
                && [Calendar Day] > DATEADD ( LastDate_; -3; MONTH )
        )
    )

 

Similar to yours, but i changed the table for AVERAGEX to iterate over to the month values. Also changed the calcualte filter a little bit.

View solution in original post

Highlighted
Helper I
Helper I

Re: Rolling average last 3 months

Excellent Ulf, that solved it 🙂

Thanks.

 

View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Rolling average last 3 months

I wrote this, Rolling Months Quick Measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499#M124

 

Also, if that doesn't work, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Responsive Resident
Responsive Resident

Re: Rolling average last 3 months

How about this?

 

Rolling Average 3 months =
VAR LastDate_ =
    LASTDATE ( Table[Calendar Day] )
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( 'Table'[Month] ); CALCULATE ( SUM ( 'Table'[Sales] ) ) );
        FILTER (
            ALL ( Table );
            [Calendar Day] <= LastDate_
                && [Calendar Day] > DATEADD ( LastDate_; -3; MONTH )
        )
    )

 

Similar to yours, but i changed the table for AVERAGEX to iterate over to the month values. Also changed the calcualte filter a little bit.

View solution in original post

Highlighted
Helper I
Helper I

Re: Rolling average last 3 months

Hi Ulf,

Brilliant! it works. 🙂

Thanks a lot.

The only remark I have is, that the rolling average doesn't react to the filters I have in the PBI report.

The value "Table[Sales]" is part of you calculation, but if I fx.filter on "Sales area" the "Sales" per month respond accordingly, but the rolling average remains the same. I find this a bit strange.

Can you tell me why?any ideas?

Thanks anyway.

Br,

Jakob

Highlighted
Responsive Resident
Responsive Resident

Re: Rolling average last 3 months

Yes, it's because of ALL(Table) in the filter. All filters are removed to be able to access previous months. It should be possible to modify the CALCULATE / FILTER expression to only remove the filters on date/year/month. To do that easier and better I suggest creating a connected date table instead of having the date columns in the "fact" table. Dimensional models are usually easier to work with in Power BI! Then you can do the same thing as now but on the date table  - ALL([NewDateTable]) instead of ALL(Table).

Highlighted
Helper I
Helper I

Re: Rolling average last 3 months

Excellent Ulf, that solved it 🙂

Thanks.

 

View solution in original post

Highlighted
Helper I
Helper I

Re: Rolling average last 3 months

Hi Greg,

thanks for your input. much appreciated. The solution didn't quite solve my problem, but I got wiser on DAX. 🙂

 

Highlighted
Responsive Resident
Responsive Resident

Re: Rolling average last 3 months

Good to hear the problem is solved! By the way, you should mark my answer as the answer, not your own post... 🙂

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021