Forum Discussion

jh292's avatar
jh292
Icon for Helper I rankHelper I
4 years ago
Solved

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
Need Help
  • jh292's avatar
    jh292
    4 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      jh292
      Icon for Helper I rankHelper I
      4 years ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        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's avatar
    jh292
    Icon for Helper I rankHelper I
    4 years ago

    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)