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:

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

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

Thanks, i added the pibx

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.