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    Running      End totaal     % of total       difference
           total
2018   5358            X                   Y                    Z
2017   5031            8457             0,595             3426
2016   5855            8413             0,696             2558
2015   5433            7908             0,687             2475
2014   5351            7658             0,699             2307
 
in the table the running total is the running total of each year per month which i calculate with:
CALCULATE(
 sum('Intel'[Balance]);
    DATESYTD(Calendar[Date]); Month(Calendar[Date]) < MONTH(NOW())
 )
 
end total is the end total for each year which is a simple
SUM('Intel'[Balance])
 
% of total is the percentage of the difference between the running total that year and the and total
Which I get by dividing the running total by the end total
 
and difference is the difference between the running total and the total each year (end total - running total)
 
What I need to calculate is X by first determinating Y, and Y is the average of the difference over the preceding 4 years of that year

So in the end X should be 8007
Y should be 0,669
and Z should be 2649
 
any ideas on how to solve this?
 
Thanks,
Marc
 
Sample pibx:
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

     

9 Replies

  • 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.

      • 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