Forum Discussion
7 Day moving Average
Hello, I am new to power bi but I am having hard time finding the 7 moving avg of a measurement(Avg Tv Sold):
So far the dax formula i've been using which is not working correctly is:
Avg Last7days = CALCULATE( AVERAGEX(VALUES(TVDATA[Avg Tv Sold]), DATESINPERIOD(Date[Sold_Date], LASTDATE(Date[Sold_Date]), -7, DAY) )
Hi, Anonymous ;
You could modify the measure as follows:
AVG = CALCULATE(AVERAGEX('Table',[AVG Tv Sold2]),DATESINPERIOD('Table'[Date],MAX('Table'[Date]),7,DAY))The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
Anonymous , Assume you have measure TV Sold
Avg Last7days =CALCULATE(
AVERAGEX(VALUES(Date[Sold_Date]),[Tv Sold]),
DATESINPERIOD(Date[Sold_Date], LASTDATE(Date[Sold_Date]), -7, DAY))
I think I figured out that AverageX is not including zeros values. For example on
Date AVG Tv Sold 1/2/2020 20 1/3/2020 30 1/4/2020 0 1/5/2020 0 1/6/2020 70 1/7/2020 22 1/8/2020 50 1/9/2020 10 1/10/2020 20 for actual moving AVG should of been:
27.42857 26 24.57143 28.66667 34.4 25.5 26.66667 15 20 But with AVERAGEX would only do those days with sales above zeros. how do i include the zeros into the calculation?
Thank You!
v-yalanwu-msftCommunity Support
Hi, Anonymous ;
You could modify the measure as follows:
AVG = CALCULATE(AVERAGEX('Table',[AVG Tv Sold2]),DATESINPERIOD('Table'[Date],MAX('Table'[Date]),7,DAY))The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
