creating new columns or rows dynamically.

[prpatel]

I have a project portfolio that has several columns namely:

• Project name
• Project owner
• Cost
• Start date
• end date

I have to take the cost, divide it to get the cost per day, then multiply by days / month and finally show the cost for each month during that project range.

As an example: this is what I have...

Project ID	Profit Center	Milestone	Cost	Start date	End date
1	Corp.	Testing	$            11,000	11-02-22	30-08-22

this is what I need to create:

Project ID	Profit Center	Milestone	Cost	Start date	End date	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22
1	Corp.	Testing	$            11,000	11-02-22	30-08-22	1,707	2,940	2,844	2,940	(2,769)	1,696	1,642

In this example.  I have a project that starts in Feb and runs thru end of August --> 7 months.

I want to find the difference (in days) from start to end {using datetimediff formula}  then divide by cost to get cost/day.

Then I need to allocate that cost/day for every month by summing to all days in the month, until the last month.

The start and end will not be static.  Some projects take 2 months, some 2 years. 

I'm not sure if there is a simple way of doing this.

Any insights would be greatly appreciated.

Thanks.

-prpatel. 

[mpizzorusso]

One method is to create a dynamic calendar with one row for every possible start or end date and all the dates in between. We then append this to the original data then filter out any days that don't fall between the start and end date of a project (be careful here because you will effectively be multiplying the number of projects by the number of days and can quickly end up with millions of rows). You can then determine the percentage of the total project days that fall in each month and multiply that by the total cost. Then just crosstab the data to return it to columns and join it back to the original and you're all set. See attached workbook for details.

[MattBSlalom]

In this solution, I've used a similar theory as described by @mpizzorusso, but with improved performance & streamlined workflow.  Since we do not care about a day-by-day analysis, we can generate just a row per month of the project then use the Cross Tab to switch those monthly rows into columns.  Note that to get the monthly cost columns in the correct order (while using the naming format you provided "mmm-yy") requires the use of some sortable value going into the Cross Tab, then a Dynamic Rename after the fields have been created to get then header values as desired.

Hope this helps.  The big concern I have is that your example allocations do not appear to be the simple "cost per day" x "days in the month" calculation that you described.

[prpatel · Accepted solution]

I used a slightly different approach.

I calculated the number of months between start month and end month then added 1.  Next using the 'generate row' tool, I created a new field, making the initial value the month of the start date (as a number 1-->12), then incremented it by 1 until I hit the  number of months., and that did it.  

Thank you all for your help.

-prpatel.