cancel
Showing results for
Did you mean: 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  Microsoft

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: 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.
9 REPLIES 9  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. Frequent Visitor

So, any idea Daniel?  Microsoft

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: 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. Frequent Visitor

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  Microsoft

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. Frequent Visitor

Hi Daniel,

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

thanks a lot!

Regards,

Marc Frequent Visitor

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

You can upload the file to One Drive,DropBox Drive or another  and publish the link. Frequent Visitor Announcements #### 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. #### 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

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
Users online (1,772)