Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Rolling 6 month Average

Hi Experts

 

i am trying to work out the rolling 6 month average value (unable to provide data) Sorry. In the screen image below i am getting the same result as the Result Column the result i am expecting the Rolling 6 month column is the average of 19256220 + 18997872 = 1917046. 

 

 

My measure is

 

Rolling 6 month =

VAR _ selcteddate = Calculate(selectedvalue(Llo_Data[Date]))

VAR _Result =
IF(Not(
Llo_Data[Alloc in month] = 0) && Not(Isblank(Llo_Data[Alloc in month])),
Calculate(Averagex(Llo_Data,[Result]),
Filter(
Llo_Data,
Llo_Data[Date] >= DateaddLlo_Data[Date],-6,MONTH) &&
Llo_Data[Date] <= -Selecteddate
Return

_Result

 

 

Need Help

8 Replies

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

    Anonymous , Try a new measure like

     

    Calculate(Averagex(values('Date'[Month Year]), calculate(max(Llo_Data,[Result]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

     

     

    change this as per need -calculate(max(Llo_Data,[Result]))

    example -calculate(sum(Llo_Data,[Result])) 

    If it is not measure then use calculate

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Amit its a Calculated Column that i need

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

        Anonymous , Try a columns like

         


        Create a rank column on year month
        rank = rankx(Table, [Yearmonth], ,asc)


        Averagex(filter(Table, [rank] >= earlier([rank]) -6 && [rank] <= earlier([rank])) ,Llo_Data[Result])