cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcobelens
Frequent Visitor

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:
1 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:

1.PNG

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

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
v-danhe-msft
Microsoft
Microsoft

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

1.PNG

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

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Daniel,

 

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

 

thanks a lot!

 

Regards,

Marc

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

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

mcobelens
Frequent Visitor

Thanks, i added the pibx

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors