Spreading Project Budget Across Weeks

Hi All,

HELP!! I need to take a project budget and work out the weekly burn rate. I have managed to calculate the weekly burn rate, but I cannot work out how to make the weekly burn rate cumulative throughout the whole of the project. Also, the output needs to produce a progressive gradient line on the line graph visuals.

The core data from our CRM system contains the project start date and the project duration in weeks. I have managed to create a project end date.

I have used similar topics to try and help me, but the below error is appearing with the Crossjoin measure/column. The suggested script was:

Budget =

FILTER(

CROSSJOIN(CRMSales ,FILTER('Calendar', DAY('Calendar'[Date]) = 1)),

'Calendar'[Date] >= DATE(YEAR(CRMSales[Start Date]), MONTH(CRMSales[Start Date]), 1)

&& 'Calendar'[Date] <= ENDOFMONTH(CRMSales[End Date], 0)

)

The error appearing is:

"Too many arguments were passed to the ENDOFMONTH function. The maximum argument count for the function is 1." 

My matrix table currently looks similar to the below example (Only showing the weekly burn rate on the project end date): 

Proposal             Week 10          Week 11          Week 12          Week 13          Week 14

ABV-XXXX           £10,000       

ABV-XXXX                                   £12,000

ABV-XXXX                                                                                   £13,000

Whereas, I want it to show the weekly burn rate each week during the project, like below:

 Proposal             Week 10          Week 11          Week 12          Week 13          Week 14

ABV-XXXX           £10,000           £10,000

ABV-XXXX                                   £12,000           £12,000            £12,000

ABV-XXXX                                                                                   £13,000            £13,000   

I have a pretty extensive calendar table, so I don’t think my problems relate to that.

If anyone could help me, I would be very grateful.

Regards,

Ashley