extrapolating by using a moving average

[mcobelens]

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:

https://1drv.ms/u/s!AivJMZgLLuT8jLIbdUbjj-h6aTKUzw

[v-danhe-msft]

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]

So, any idea Daniel?

[v-danhe-msft · Accepted solution]

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]

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]

Hi @mcobelens,

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

Regards,

Daniel He

[mcobelens]

Hi Daniel,

Yeah, I thought so but thanks for your help anyway. I think your solution will also suffice.

thanks a lot!

Regards,

Marc

[mcobelens]

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

[Anonymous]

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

[mcobelens]

Thanks, i added the pibx