DAX: Creating a new table with months between dates and value equally divided by those months

[ashleylinkewich]

Hi community,

Sorry for the poor title - I'm not sure how else to describe this problem. 

I am working on a project where I have a table that looks like this:

Project	Start Month	End Month	Value
Project A	Jan 2022	March 2022	120
Project B	Dec 2021	Feb 2022	250
Project C	April 2022	July 2022	500

In order to forecast these values by month, I want to create a visual that has the value equally divided by the months the project spans. To do this, I anticipate I will need a new table that looks like this:

Project	Month	Value
Project A	Jan 2022	40
Project A	Feb 2022	40
Project A	Mar 2022	40
Project B	Dec 2021	83.33
Project B	Jan 2022	83.33
Project B	Feb 2022	83.33
Project C	April 2022	125
Project C	May 2022	125
Project C	June 2022	125
Project C	July 2022	125

Basically, I believe I need a table where each row is a month from each project (based on the start and end months) and the value divided equally between those months.

Does anyone have suggestions on how to solve this, or the DAX required? Also open to other solutions!

Cheers,

[amitchandak]

@ashleylinkewich , refer to the blog or the file attached for a similar solution

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...