Forum Discussion
Solved
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 manage...
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 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.