Forum Discussion

mcobelens's avatar
mcobelens
Frequent Visitor
7 years ago
Solved

extrapolating by using a moving average

Hi there,   I could really need some help with the following problem.   I need to extrapolate a total of the current year on the basis of the average of the preceding 4 years.   Year    Runnin...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi mcobelens,

    Based on my test, you coud refer to below formula:

    X = CALCULATE([Running total upto current month for each year],'Calendar'[Year]=2018)/[Y]
    Y = var a=CALCULATE([% of totaal],FILTER('Calendar','Calendar'[Year]=2014))
var b=CALCULATE([% of totaal],FILTER('Calendar','Calendar'[Year]=2015))
var c=CALCULATE([% of totaal],FILTER('Calendar','Calendar'[Year]=2016))
var d=CALCULATE([% of totaal],FILTER('Calendar','Calendar'[Year]=2017))
return (a+b+c+d)/4
    Z = [X]-CALCULATE([Running total upto current month for each year],'Calendar'[Year]=2018)

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

     

v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
7 years ago

Hi mcobelens,

From your description, I could not find any information about the 'Intel'[Balance], and I could not understand your data structure, if you want to calculate the End total, the X should be calculated like the other value in this column, could you please offer me more information and share the pbix file if possible?

 

Regards,

Daniel He

mcobelens's avatar
mcobelens
Frequent Visitor
7 years ago

So, any idea Daniel?

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi mcobelens,

    Based on my test, you coud refer to below formula:

    X = CALCULATE([Running total upto current month for each year],'Calendar'[Year]=2018)/[Y]
    Y = var a=CALCULATE([% of totaal],FILTER('Calendar','Calendar'[Year]=2014))
var b=CALCULATE([% of totaal],FILTER('Calendar','Calendar'[Year]=2015))
var c=CALCULATE([% of totaal],FILTER('Calendar','Calendar'[Year]=2016))
var d=CALCULATE([% of totaal],FILTER('Calendar','Calendar'[Year]=2017))
return (a+b+c+d)/4
    Z = [X]-CALCULATE([Running total upto current month for each year],'Calendar'[Year]=2018)

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

     

    • mcobelens's avatar
      mcobelens
      Frequent Visitor
      7 years ago

      Hi Daniel,

       

      Thanks a lot, that is sort of what I need but is there a way to incorporate these results in the matrix?

       

      Regards,

      Marc

       

       

      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        Hi mcobelens,

        I am afraid you could not get the values in the Maxtix due to the logic is different. 

         

        Regards,

        Daniel He