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
ae1999
Regular Visitor

measure rolling 12 month average (returning blank when there are not enough months)

Dear all, 

 

Apologies for this question, I see it is fairly common, but after spending a lot of time trying different methods I still couldn't figure it out completely. 

 

I have a table with values and dates (I also have a date table).  See the table below as sample data. The third colomn is what I am trying to achieve.  I want to calculate a 12 month moving average, where the values are blank if there is not enough data to calculate over the 12 months.  I found this question at this forum the most helpful but still couldn't fully figure it out.  

 

Thanks a lot for your help

 

 

DateValues12 month moving average 
jan 2019589 
feb 2019534 
mar 2019456 
apr 2019574 
may 2019599 
jun 2019514 
jul 2019545 
aug 2019 461 
sep 2019487 
oct 2019515 
nov 2019522 
dec 2019542528
jan 2020550524
feb 2020482520
mar 2020499524

 

 

 

 

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @ae1999 
Please refer to attached sample file with the solution

1.png

12 month moving average = 
VAR FirstDateWithData = CALCULATE ( MIN ( Sales[Order Date] ), REMOVEFILTERS ( ) )
VAR ReferenceDate = EOMONTH ( FirstDateWithData, 11 )
VAR CurrentDate = MAX ( 'Date'[Date] )
RETURN
    IF (
        CurrentDate >= ReferenceDate,
        CALCULATE ( 
            AVERAGEX ( VALUES ( 'Date'[Year Month] ), [Values] ),
            DATESINPERIOD ( 'Date'[Date], CurrentDate, -1, YEAR )
        )
    )

View solution in original post

6 REPLIES 6
v-zhangti
Community Support
Community Support

Hi, @ae1999 

 

You can try the following methods.

12 month moving average = 
Var _N1=SUMMARIZE(FILTER('Table',[Date]<=EARLIER('Table'[Date])),[Date],"Sum",SUM('Table'[Values]))
Var _N2=TOPN(12,_N1,[Date],DESC)
Var _Average=DIVIDE(SUMX(_N2,[Sum]),12)
return
IF(COUNTX(_N2,[Sum])<12,BLANK(),_Average)

vzhangti_0-1672305089688.png

Measure = 
Var _N1=SUMMARIZE(FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Table'[Date])),[Date],"Sum",SUM('Table'[Values]))
Var _N2=TOPN(12,_N1,[Date],DESC)
Var _Average=DIVIDE(SUMX(_N2,[Sum]),12)
return
IF(COUNTX(_N2,[Sum])<12,BLANK(),_Average)

vzhangti_1-1672305296954.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

tamerj1
Super User
Super User

Hi @ae1999 
Please refer to attached sample file with the solution

1.png

12 month moving average = 
VAR FirstDateWithData = CALCULATE ( MIN ( Sales[Order Date] ), REMOVEFILTERS ( ) )
VAR ReferenceDate = EOMONTH ( FirstDateWithData, 11 )
VAR CurrentDate = MAX ( 'Date'[Date] )
RETURN
    IF (
        CurrentDate >= ReferenceDate,
        CALCULATE ( 
            AVERAGEX ( VALUES ( 'Date'[Year Month] ), [Values] ),
            DATESINPERIOD ( 'Date'[Date], CurrentDate, -1, YEAR )
        )
    )

Dear Tamerj1, 

 

This turned out great, thanks a lot! 

 

FreemanZ
Super User
Super User

hi @ae1999 

 

Hope "Jan 2019" is just a data format and suppoing your date look like this:

DateValues12 month moving average 
1/1/2019589 
2/1/2019534 
3/1/2019456 
4/1/2019574 
5/1/2019599 
6/1/2019514 
7/1/2019545 
8/1/2019461 
9/1/2019487 
10/1/2019515 
11/1/2019522 
12/1/2019542528
1/1/2020550525
2/1/2020482521
3/1/2020499524

 

try to add a column like this:

 

MovingAvg = 
VAR _date = [Date]
VAR _table =
FILTER(
    data,
    data[Date]>=EDATE(_date, -11)
    &&data[Date]<=_date
)
VAR _sum =
CALCULATE(
    SUM(data[Values]),
    ALLEXCEPT(data, data[Date]),
    _table
)
VAR _month = COUNTROWS(_table)
RETURN
IF(
    _month=12,
    DIVIDE(_sum, 12)
)

 

 

it shall work like this:

FreemanZ_1-1672232034371.png

 

ae1999
Regular Visitor

Dear @Greg_Deckler , 

 

Thanks a lot for your reply, this is indeed a better way to create a moving average. 

I was wondering how I modify this dax to not return a value/ to return blanks when there are not enough periods to calculate the rolling average, like my example? 

 

To illustrate in your example it should not return a value for the first 2 months since March is the first month we got 3 months of data. I was trying with an if statement but couldn't fully get it working. 

 

Thanks a lot again! 

Greg_Deckler
Super User
Super User

@ae1999 Try this: Better Rolling Average - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Top Solution Authors