Forum Discussion

mcaldwell's avatar
mcaldwell
Frequent Visitor
4 years ago
Solved

Rolling Average needs to exclude current month

Hello, I have the following measure that is calculating a rolling 6 month average however it includes any current month data and if an account does not have any current data it does not appear in my...
  • amitchandak's avatar
    amitchandak
    4 years ago

    mcaldwell , Try like

     

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

     

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

mcaldwell's avatar
mcaldwell
Frequent Visitor
4 years ago

With a minor tweek I was able to get one of amitchandak suggestions to work.  Here is where I landed.

 
Moving AverageX =
CALCULATE(sum(invoice_metrics[Correct_FTDue]),DATESINPERIOD('Calendar'[Date],eomonth(MAX('Calendar'[Date]),-1),-5,MONTH))
/(
Calculate(
DISTINCTCOUNT(BillingPeriod[Label]),
DATESINPERIOD('Calendar'[Date],eomonth(MAX('Calendar'[Date]),-1),-5,MONTH)))

 

 

Moving AverageX =
CALCULATE(sum(invoice_metrics[Correct_FTDue]),DATESINPERIOD('Calendar'[Date],eomonth(MAX('Calendar'[Date]),-1),-5,MONTH))
/(
Calculate(
DISTINCTCOUNT(BillingPeriod[Label]),
DATESINPERIOD('Calendar'[Date],eomonth(MAX('Calendar'[Date]),-1),-5,MONTH)))
 
Thank you!! I'm going to get lots of Diet Coke for this, while I can't share the pop, I will make sure you get most of the credit!