Seasonal Budget vs. Actuals

bhorneber — Fri, 19 Apr 2019 17:13:47 GMT

Hi,

I'm trying to visualize a "simple" Actuals vs. Budget chart. Actuals are available every month, buget once a year.
I can easily add a linear budget by dividing the sum by 12:

What I want to get to is this: The budget is multiplied by the relative sum of actuals per month.

Or in other words: Let's say May contributes 5% in 2017 and 15% in 2018 to the yearly total. So in average, I would expect 10% of the budget in 2019 to be shown in May.

What I cannot figure out is how to write the set analysis to calculate this 10% and apply it to the May values (and other months respectively).

Attached is some sample data.
It contains two countries, that might have different seasonal effects, so the seasonality factor should be calculated depending on the selection.

Appreciate any suggestions or ideas!

I'm currently playing around with formulas like this to get the total for a month from 2017 and 2018. Having this, I could divide it by the total actuals for those two years:

Sum(Aggr(Sum({<Year={2017,2018}>} Actuals), Month))

When displaying this data in a table, it does only return values for 2017, so something seems to be missing:

topic Seasonal Budget vs. Actuals in App Development

Seasonal Budget vs. Actuals