Forum Discussion
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...
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)/4Z = [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
So, any idea Daniel?
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
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
Hi mcobelens,
I am afraid you could not get the values in the Maxtix due to the logic is different.
Regards,
Daniel He
Hi Daniel,
Yeah, I thought so but thanks for your help anyway. I think your solution will also suffice.
thanks a lot!
Regards,
Marc