Forum Discussion
Budget Split over months
Dear all,
I am hopinmg that someone can assist as i am new to BI.
I have a set of data which shows a few different schemes with their yearly allocated budget shown against them. I have managed to calulate the daily burn rate by dividing the budget by the number of working days the task takes. (blue data).
Ultimately what i require is on the right under the green columns which shows the budget split equally over the months where the works are planned.
Any help on this would be greatly appreciated. Happy to provide more info if needed.
Joe
| Programme | Start Date | Finish Date | Allocated Budget | Task Duration | Daily Burn Rate | Feb | Mar | Apr | May |
| Scheme 1 | 01/02/2021 | 12/02/2021 | 199,340 | 10 | 19,934 | 199,340 | |||
| Scheme 2 | 15/02/2021 | 15/03/2021 | 154,700 | 20 | 7,735 | 77,350 | 77,350 | ||
| Scheme 3 | 22/02/2021 | 15/03/2021 | 50,000 | 15 | 3,333 | 25,000 | 25,000 | ||
| Scheme 4 | 22/02/2021 | 26/11/2021 | 400,000 | 200 | 2,000 | 40,000 | 40,000 | 40,000 | 40,000 |
| Scheme 5 | 01/03/2021 | 05/03/2021 | 1,000,000 | 5 | 200,000 | 1,000,000 | |||
| Scheme 6 | 01/03/2021 | 19/03/2021 | 87,000 | 15 | 5,800 | 87,000 |
Hi JoeHazelton ,
Create columns as below.
feb = IF(2>=MONTH('Table'[Start Date])&&2<=MONTH('Table'[Finish Date]),'Table'[Allocated Budget]/(DATEDIFF('Table'[Start Date],'Table'[Finish Date],MONTH)+1),BLANK()) mar = IF(3>=MONTH('Table'[Start Date])&&3<=MONTH('Table'[Finish Date]),'Table'[Allocated Budget]/(DATEDIFF('Table'[Start Date],'Table'[Finish Date],MONTH)+1),BLANK()) apr = IF(4>=MONTH('Table'[Start Date])&&4<=MONTH('Table'[Finish Date]),'Table'[Allocated Budget]/(DATEDIFF('Table'[Start Date],'Table'[Finish Date],MONTH)+1),BLANK()) may = IF(5>=MONTH('Table'[Start Date])&&5<=MONTH('Table'[Finish Date]),'Table'[Allocated Budget]/(DATEDIFF('Table'[Start Date],'Table'[Finish Date],MONTH)+1),BLANK())Best Regards,
Jay
7 Replies
JoeHazelton , refer if approach these blogs can help
Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-Convert-to/ba-p/1657798
Hi Amit,
Thank you for youre reply. I found the links very useful and it gave me more direction than i had. However as i am still trying to understand relationship between tables im still struggling to get my end result.
I would love my matrix table to show Schemes on the (Y axis) and values per month split equally across the months that the work is undertaken (x axis). I have attached my Pbix file for you to view. I have manually created my calendar table but its this last step im stuck on.
I have calculated the amount of months that the work takes (DATEDIFF) and the number of days but i need the yearly allocated budget split evenly across the mohts of the work,
I hope this makes sense. Would be great for some further pointers.
For some reason this forum will not let me add my pbix file. I have provided a screen shot below.
Joe
You can start with a datediff between start and finish and divide the budget by the number of months. You can use an IF statement to check if that value should be displayed (aka the month is within the timeframe).
Hi Steve,
Sounds a like a plan, although i wouldnt know where to start as my DAX is very basic. Would you be able to provide an example of these which i could then adapt and apply?
Thanks
You can start by writing this datediff measure. Something likeDATEDIFF(MIN( Calendar[Start Date] ), MIN( Calendar[Finish Date]), MONTH ).
And see if this works. Then divide the measure with your budget by the previous result.
One step at a time..
Hi JoeHazelton ,
Create columns as below.
feb = IF(2>=MONTH('Table'[Start Date])&&2<=MONTH('Table'[Finish Date]),'Table'[Allocated Budget]/(DATEDIFF('Table'[Start Date],'Table'[Finish Date],MONTH)+1),BLANK()) mar = IF(3>=MONTH('Table'[Start Date])&&3<=MONTH('Table'[Finish Date]),'Table'[Allocated Budget]/(DATEDIFF('Table'[Start Date],'Table'[Finish Date],MONTH)+1),BLANK()) apr = IF(4>=MONTH('Table'[Start Date])&&4<=MONTH('Table'[Finish Date]),'Table'[Allocated Budget]/(DATEDIFF('Table'[Start Date],'Table'[Finish Date],MONTH)+1),BLANK()) may = IF(5>=MONTH('Table'[Start Date])&&5<=MONTH('Table'[Finish Date]),'Table'[Allocated Budget]/(DATEDIFF('Table'[Start Date],'Table'[Finish Date],MONTH)+1),BLANK())Best Regards,
Jay
Hi Jay,
Really appreciate a different view on this. I have made my report now work the way it needs to.
Thank you so much for your support.
