cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Rolling 3 month average - 3 months

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
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
1 ACCEPTED SOLUTION
Helper I

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)
6 REPLIES 6
Helper I

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)

Super User

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

Helper I

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

Super User

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

Helper I

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