Last X months rolling average but on a weekly level

[Bratone]

Hello,

I am trying to create a measure which will give me the rolling average fot the past X months, but on a weekly level, not on a daily level. Why am I mentioning this is because I tried the following formula:

DA Prod% 1M MA=

AVERAGEX(DATESINPERIOD('cCalendar'[Date],LASTDATE(RAR[Date]),-3,MONTH), [Total Activities])

The problem with this is that it returns the average value on a daily basis: 

So basically every value that you see in the above tabel, should be multiplied by 5 (the usual number of working days in a week).

Any idea how I will be able to get those values ? I know I can maybe add a *5 in the formula but that would not be sustainable for the situations where I have less than 5 working days.

Thank you! 

[amitchandak · Accepted solution]

@Bratone , Try like

DA Prod% 1M MA=
calculate( AVERAGEX(values('cCalendar'[Month Year]) , [Total Activities]),DATESINPERIOD('cCalendar'[Date],LASTDATE(RAR[Date]),-3,MONTH) )

[Bratone]

Thank you so much,

I actually changed the part in formula from (values('cCalendar'[Month Year]) to (values('cCalendar'(Week Year]) and now it displays correctly.

One more question, do you know how can I exclude the values for which I don't have the data available for the past 3 months ? For example, July, and August shouldn't be there because they don't have 3 months old of historical data for which to calculate the measure.