So this again will require something different, and actually change it up quite a bit.
Make sure to check out all the mini series tutorials here. There’s nearly all budgeting techniques you can think all covered here
Your model here and the data within your tables will need to be more standardized to get this to work properly.
That’s the reality here.
There’s no special technique to solve it other than sorting out the data.
I’ve tried to work something up here but want you to have a think about how you can simplify things.
Personally I don’t like to put bandaids around the model and DAX formula, because it lifts the complexity of everything when you can just make simple change to your raw data.
For example, this here should really be broken down by month
Then you can actually solve this very quickly using the techniques described in here
Within your actuals table you actually have a sound setup
In your allocation table you already have a good monthly breakdown so that fine.
This idea to join up the date table in some way create a common column between all the key table here.
I believe it would be this one.
So the idea would be to get this into the date table somehow. You could do it simply with a calculated column like this
YearMonth = LEFT( Dates[MonthnYear], 6 )
With this we could then easily connect it to the other table using the TREATAS function.
So in summary the important thing here is to improve the data. This will make this 10 times easier to implement and understand.
Want to work on that first.
Then definitely check out the monthly allocation tutorial. The technique showcased there is how you would then ultimately solve this.