Forum Discussion
DAX: Creating a new table with months between dates and value equally divided by those months
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,
1 Reply
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-or-end-date/ba-p/1503785
