Forum Discussion
Daily Moving Average - Hour on x-axis?
So I have a working daily moving average calculation below:
Moving Avg Daily Revenue =
AVERAGEX(
DATESINPERIOD(
'Dates'[date],
LASTDATE('Dates'[date]) ,
-28 ,
DAY),
CALCULATE(SUM('Transactions'[Revenue] )
))
Currently, the transaction table is connected to the Date table via a transaction date column. I extracted the date and time from this column in the transaction table. I created another column that rounds the time to the nearest hour. That column is what is currently being displayed on my x-axis. I would like to see in a moving average calculation, the moving average of sales over x days that happened in the 8 AM hour, the 9 AM hour, 10 AM hour, etc...
I haven't dealt with time too much in Power BI. How would I handle this?
2 Replies
PowerUser123 , Create a timetable and join with that
https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/
Moving Avg Daily Revenue =
AVERAGEX(
DATESINPERIOD(
'Dates'[date],
LASTDATE('Dates'[date]) ,
-28 ,
DAY),
CALCULATE(SUM('Transactions'[Revenue]), filter(all(time), time[hour]<=max(Taime[hour]) )
))Hi PowerUser123
Have you solved this question with amitchandak's help? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.
If you need more help, please provide
(1) a sample file, you can replace raw data with bogus data to protect your privacy.
or provide some sample data that fully covers your issue/question.
(2) give your expected result based on the sample you provide.
Thanks.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
