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

    Hi Daniel, How can I upload a pibx? I created an example but have no ideo on how to upload it?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      You can upload the file to One Drive,DropBox Drive or another  and publish the link.

      • mcobelens's avatar
        mcobelens
        Frequent Visitor
        7 years ago

        Thanks, i added the pibx

  • 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