Pro rate project value over a number of months

[ghdunn]

I am looking for various (ideally simple/elegant) ways of pro-rating project value over months

Ideally Power Query then DAX but happy if DAX is simpler.

Project Value £1m

Start date 1/Jan

End date 31/May

Simplistically you could say that would be spread 

Jan £200k

Feb £200k

Mar £200k

Apr £200k

May £200k

However, the mechanics of that might be more complicated than the answer...what to do if the start/end dates were not whole months etc. 

You could do it with by Project Value/(EndDate-StartDate)*Number of days in month.

Or similarly, but only using WorkDays

As I say..open to any and all ideas - i am just demoing options to a client.

[Jimmy801 · Accepted solution]

Hello @ghdunn 

I've prepared a solution that adds a new custom column, that does the whole calculation - meaning devides the amount of the project to the dates of the project and then groups back to months/years. Here the complete solution

let
	Source = #table
	(
		{"Project name","Project amount","Start Date","End Date"},
		{
			{"A","500000","1.1.20","15.02.20"}
		}
	),

    ChangedType = Table.TransformColumnTypes(Source,{{"Project name", type text}, {"Project amount", type number}, {"Start Date", type date}, {"End Date", type date}}),
    AddedTable = Table.AddColumn
	(
		ChangedType, 
		"Custom", (add)=> let 
			AddDates = Table.FromColumns({List.Dates(add[Start Date],Duration.TotalDays(add[End Date]-add[Start Date])+1,#duration(1,0,0,0))},{"Date"}),
			AddColumn = Table.AddColumn(AddDates, "Month/Year", each Text.From(Date.Month(_[Date])) & "/" & Text.From(Date.Year(_[Date]))),
			AddDayValue = Table.AddColumn(AddColumn, "DayValue", each add[Project amount]/Table.RowCount(AddDates)),
			Group = Table.Group(AddDayValue, {"Month/Year"}, {{"Monthly value", each List.Sum(_[DayValue])}})
		in 
			Group
	),
    ExpandedCustom = Table.ExpandTableColumn(AddedTable, "Custom", {"Month/Year", "Monthly value"}, {"Month/Year", "Monthly value"}),
    ChangedType2 = Table.TransformColumnTypes(ExpandedCustom,{{"Monthly value", type number}})
in
    ChangedType2

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Jimmy801 · Accepted solution]

Hello @ghdunn 

I've prepared a solution that adds a new custom column, that does the whole calculation - meaning devides the amount of the project to the dates of the project and then groups back to months/years. Here the complete solution

let
	Source = #table
	(
		{"Project name","Project amount","Start Date","End Date"},
		{
			{"A","500000","1.1.20","15.02.20"}
		}
	),

    ChangedType = Table.TransformColumnTypes(Source,{{"Project name", type text}, {"Project amount", type number}, {"Start Date", type date}, {"End Date", type date}}),
    AddedTable = Table.AddColumn
	(
		ChangedType, 
		"Custom", (add)=> let 
			AddDates = Table.FromColumns({List.Dates(add[Start Date],Duration.TotalDays(add[End Date]-add[Start Date])+1,#duration(1,0,0,0))},{"Date"}),
			AddColumn = Table.AddColumn(AddDates, "Month/Year", each Text.From(Date.Month(_[Date])) & "/" & Text.From(Date.Year(_[Date]))),
			AddDayValue = Table.AddColumn(AddColumn, "DayValue", each add[Project amount]/Table.RowCount(AddDates)),
			Group = Table.Group(AddDayValue, {"Month/Year"}, {{"Monthly value", each List.Sum(_[DayValue])}})
		in 
			Group
	),
    ExpandedCustom = Table.ExpandTableColumn(AddedTable, "Custom", {"Month/Year", "Monthly value"}, {"Month/Year", "Monthly value"}),
    ChangedType2 = Table.TransformColumnTypes(ExpandedCustom,{{"Monthly value", type number}})
in
    ChangedType2

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ghdunn]

Outstanding...much more elegant than the cross table route I was going down.  

Very many thanks @Jimmy801 

[Jimmy801]

Hello @ghdunn 

your feedback is much appreciated. I'm glad it worked out

BR

Jimmy

[ghdunn]

@Jimmy801 

Just a quick follow on question...is this code convertible to 'working days'?

I found this:  

https://www.powerquery.training/networkdays/

and am preparing to work through it, but i thought I would ask first 🙂

Ged

[Jimmy801 · Accepted solution]

Hello @ghdunn 

everything is possible. 🙂

the dates are calculated within this step of the AddColumn

AddDates = Table.FromColumns({List.Dates(add[Start Date],Duration.TotalDays(add[End Date]-add[Start Date])+1,#duration(1,0,0,0))},{"Date"}),

and instead of just doing a List.Dates, add a List.Select to check if the Day is a weekday. 

Here the new solution

let
	Source = #table
	(
		{"Project name","Project amount","Start Date","End Date"},
		{
			{"A","500000","1.1.20","15.02.20"}
		}
	),

    ChangedType = Table.TransformColumnTypes(Source,{{"Project name", type text}, {"Project amount", type number}, {"Start Date", type date}, {"End Date", type date}}),
    AddedTable = Table.AddColumn
	(
		ChangedType, 
		"Custom", (add)=> let 
			AddDates = Table.FromColumns({List.Select(List.Dates(add[Start Date],Duration.TotalDays(add[End Date]-add[Start Date])+1,#duration(1,0,0,0)), each Date.DayOfWeek(_)<=4)},{"Date"}),
			AddColumn = Table.AddColumn(AddDates, "Month/Year", each Text.From(Date.Month(_[Date])) & "/" & Text.From(Date.Year(_[Date]))),
			AddDayValue = Table.AddColumn(AddColumn, "DayValue", each add[Project amount]/Table.RowCount(AddDates)),
			Group = Table.Group(AddDayValue, {"Month/Year"}, {{"Monthly value", each List.Sum(_[DayValue])}})
		in 
			Group
	),
    ExpandedCustom = Table.ExpandTableColumn(AddedTable, "Custom", {"Month/Year", "Monthly value"}, {"Month/Year", "Monthly value"}),
    ChangedType2 = Table.TransformColumnTypes(ExpandedCustom,{{"Monthly value", type number}})
in
    ChangedType2

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ghdunn]

Works a charm @Jimmy801 

I am going to push my luck one more time if you don't mind?  As I said in the OP...just exploring different options for a client but your command of this is amazing.

So the final piece is a very similar challenge...but instead of spreading an total project amount over a flexible time period, to multiply a fixed daily amount BY that flexible time period.

So instead of dividing a $100,000k budget over say Start Date 1/1/2020 End Date: 31/12/2020, multiplying $500 per working day spread over the same project duration but coming up with the monthly numbers.

Ged

[Jimmy801]

Hello @ghdunn 

i don't get the point. The output of my solution is already grouped by month (using distribution logic on workday basis).

All the best

Jimmy

[ghdunn]

The first solution(s) takes a total cost and divides by a number of days to get a variable daily cost and spreads the days over the months...PERFECT for high level project planning.

However, I am now being asked for more of a bottom up estimate:

A resource working from 23rd Feb to 16th June (working days) x a fixed rate of $500 per day...what would that add up to in terms of months.  So its converting a date range to a number of days in each month then multiplying by rate.

Ged

[Jimmy801]

Hello @ghdunn 

okay. Then i would suggest you creating a new post for this, because it's quite different. I got the approach, but don't know the columns you want to have etc. 

Jimmy