Forum Discussion

Kratos_ZA's avatar
Kratos_ZA
Helper I
4 years ago
Solved

Last 6 full months average

Hi all,

 

Kindly assist

 

How can I get the last 6 months average of lets say Table[Revenue] if current month isnt a full month (eg, today is the 12 Nov)  and we only want to calculate the last full 6 months average? i.e. between May - Oct 2021?

 

NB: I have a Period[date] table

 

Thanks

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Kratos_ZA , Try like

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

     

    Rolling 6 = CALCULATE(AverageX(values('Date'[Month]) ,calculate(Sum(Sales[Sales Amount]))) ,DATESINPERIOD('Date'[Date],eomonth(MAX(Sales[Sales Date]),-1),-6 ,MONTH))

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Kratos_ZA , Try like

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

     

    Rolling 6 = CALCULATE(AverageX(values('Date'[Month]) ,calculate(Sum(Sales[Sales Amount]))) ,DATESINPERIOD('Date'[Date],eomonth(MAX(Sales[Sales Date]),-1),-6 ,MONTH))