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.
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
Date | Values | 12 month moving average |
jan 2019 | 589 | |
feb 2019 | 534 | |
mar 2019 | 456 | |
apr 2019 | 574 | |
may 2019 | 599 | |
jun 2019 | 514 | |
jul 2019 | 545 | |
aug 2019 | 461 | |
sep 2019 | 487 | |
oct 2019 | 515 | |
nov 2019 | 522 | |
dec 2019 | 542 | 528 |
jan 2020 | 550 | 524 |
feb 2020 | 482 | 520 |
mar 2020 | 499 | 524 |
Solved! Go to Solution.
Hi @ae1999
Please refer to attached sample file with the solution
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 )
)
)
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)
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)
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.
Hi @ae1999
Please refer to attached sample file with the solution
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!
hi @ae1999
Hope "Jan 2019" is just a data format and suppoing your date look like this:
Date | Values | 12 month moving average |
1/1/2019 | 589 | |
2/1/2019 | 534 | |
3/1/2019 | 456 | |
4/1/2019 | 574 | |
5/1/2019 | 599 | |
6/1/2019 | 514 | |
7/1/2019 | 545 | |
8/1/2019 | 461 | |
9/1/2019 | 487 | |
10/1/2019 | 515 | |
11/1/2019 | 522 | |
12/1/2019 | 542 | 528 |
1/1/2020 | 550 | 525 |
2/1/2020 | 482 | 521 |
3/1/2020 | 499 | 524 |
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:
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!
@ae1999 Try this: Better Rolling Average - Microsoft Power BI Community
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.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |