Forum Discussion

Kratos_ZA's avatar
Kratos_ZA
Icon for Helper I rankHelper 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 f...
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))

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