Forum Discussion
Moving average for values with gap in dates.
Hi! First time posting here and only started last week so do help 🙂 I have a table with values such as this
| Dates | Countries | Values |
| 28.02.20 | Iran | 4 |
| 28.02.20 | Iran | 2 |
| 24.02.20 | China | 6 |
| 23.02.20 | Iran | 7 |
| 21.02.20 | China | 4 |
| 19.02.20 | Iran | 3 |
| 17.02.20 | China | 2 |
I need a line chart that depicts 3 moving averages, 7, 20 and 30 days.
So far,
I have tried the formula,
Anonymous
like
Avg 7 day = CALCULATE(sum(Table[Values]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),-7,Day))/7
or
Avg 7 day = CALCULATE(Average(Table[Values]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),-7,Day))
Avg 20 day = CALCULATE(sum(Table[Values]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),-20,Day))/20
Avg 20 day = CALCULATE(Average(Table[Values]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),-20,Day))
Use date table
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.
2 Replies
Anonymous
like
Avg 7 day = CALCULATE(sum(Table[Values]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),-7,Day))/7
or
Avg 7 day = CALCULATE(Average(Table[Values]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),-7,Day))
Avg 20 day = CALCULATE(sum(Table[Values]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),-20,Day))/20
Avg 20 day = CALCULATE(Average(Table[Values]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),-20,Day))
Use date table
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.Hi Anonymous ,
LASTDATE function is used to return the last date in the current context. It is generally used when you use month or year as the granularity for moving average. In your current situation, you only need to replace all of LASTDATE with the MAX function.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
