Moving average for values with gap in dates.

[Anonymous]

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,

CALCULATE(AVERAGEX(Query, Query[Values]), DATESINPERIOD('Calendar'[Date].[Date],LASTDATE('Calendar'[Date].[Date]), -7,DAY),

with calander[date] as my axis while creating a bidirectional relationship between my calendar table and dates,

Then filtering the countries on the line chart by visual filters but the values are wrong.

Please do help 🙂

Please do help

[v-deddai1-msft]

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

[amitchandak · Accepted solution]

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