Rolling 3 month average - 3 months

[jh292]

Hi 

I created the following measure (no doubt with help) which works perfectly for a rolling 3 month average;

3 Month Average Rolling Rev = divide(calculate([Total Sell Cost],datesinperiod('Calendar'[Date],calculate(lastdate('Calendar'[Date]),filter(ALLSELECTED('Calendar'),'Calendar'[Complete Month] = "Complete Month")),-3,month)),3,0)

My data contains sales data live so I remove anything from the current monthly by allocating a simple field in my calendar table to say whether the month is complete or not - hence the ",filter(ALLSELECTED('Calendar'),'Calendar'[Complete Month] = "Complete Month"" part... because I don't want this in the average

I am now trying to replicate this formula except for the previous 3 months,

I.e. the above shows Nov+Oct+Sep, but I want to see the result for Aug+Jul+Jun

I know that most likely I need to put in

calculate(DATEADD('Calendar'[Date],-3,MONTH)), 

or similar, somewhere but I can't work out where it goes or what to swap out for it....
any help would be great!
Thanks

[jh292 · Accepted solution]

this is how I have done it;

3 month rolling previous 4,5,6 months = divide(

CALCULATE(sumx('salesTable',if(DATEDIFF('salesTable'[date],today(),month) <= 6 &&

DATEDIFF('salesTable'[date],TODAY(),MONTH) >= 4, 'salesTable'[Selling Cost],0))),3,0)

[amitchandak]

@jh292 , rolling 3 till last month end

Rolling 3 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1) ,-3,MONTH))

Rolling 3 before 3= CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-4) ,-3,MONTH))

[jh292]

@amitchandak 
Thanks but that hasnt worked, I can't do Average(Sales[Sales Amount]) I don't think because I have multiple sales items for customers in a month which is why I used a measure* in my original formula and I need to use a column in yours 
*sum(sales amount)

[amitchandak]

@jh292 , Yes in case you need sum do that

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1) ,-3,MONTH))

Rolling 3 before 3= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-4) ,-3,MONTH))

[jh292]

@amitchandak 
Returning blank and I think that would be because the date/calendar table continues 2 years into the future (which I cant change) so it's doing -1 month from the max which is in like 2023 and obviously doesn't have sales figures
bit tricky!