Moving Average

[owlet0214]

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.

[amitchandak]

@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-Y...


Appreciate your Kudos.

[owlet0214]

Thank you, I have a meeting so I'll try it later.

[owlet0214]

Thank you very much. I was able to count well.