Forum Discussion
Moving Average
Hello,
I made a moving average with reference to "https://community.powerbi.com/t5/Desktop/Moving-Average/m-p/43041", but I can't.
I tried other methods, but I couldn't, so I'll consult you.
My DAX is below.
The table above shows the "fixday" hierarchy.
MovingAverage7day2 =
CALCULATE (
AVERAGEX ( 'Table', 'Table'[count] ),
DATESINPERIOD (
'Table'[fixday],
LASTDATE ('Table'[fixday] ),
-7,
day
)
)
When I tried with the following formula, only the specified period part was displayed,
but it seems that "SUM" does not work, and "count / 7" are lined up.
I don't think that the above formula and the formula below work for the function that aggregates the filtered periods.
CALCULATE(
SUM('Table'[count])/7,
FILTER('Table','Table'[count] < DATE(2020,05,16) && 'Table'[count] > DATE(2020,05,10)))
)
I need help.
3 Replies
owlet0214 , Join you date with date calendar and try. I assumed that fixday is a date
Rolling 7 = CALCULATE(Average('Table'[count]),DATESINPERIOD(Date[Date],MAX('Table'[fixday]),-7,Day))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
