Skip to main content
cancel
Showing results for 
Search instead 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

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

View solution in original post

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

I need Netflix for free for 3 months

amitchandak
Super User
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))

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

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors