Plotting the number of days between two dates by month

Question

I have a table of 'events' in powerpivot that contains an 'End Date', 'Start Date' and a 'Length' in days.

I can currently plot a pivot chart from this data showing the sum of 'Length' by 'End Date' or 'Start Date', but how do I create a measure that will allow me to plot the 'Length' by month, where only the number of days that fall within that month are counted?

For example, if a record has an 'End Date' of 1/11/2018, and a 'Length' of 10 days, then the measure should put 9 days in October and 1 day in November.

I have set up a 'dates' table to give me the month etc., with a relationship to the 'End Date' in the 'events' table.

Answer 1

for excel check: solution

for power bi check: Solution