Use Created Date Column to form a Predicted Close Date Column

[Anonymous]

Deleted. 

[edhans · Accepted solution]

Yes @Anonymous - try this:
You cannot add fractions of months, so I had to convert your months to average days, assuming each month has 30.4 days in it (365/12).

It returns this:

the start range is the average days in the first range of months (3/6/9) and End range is the last set of months (6/9/12). I then took the average of those two, rounded to 0 decimals, and added that number of days to the first value.

At this point, remove the columns you don't need. Here is the full code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDTNzIwMlTSUTJW0FUwU/DNzyvJKFaK1YlWMjRGkjUDyloiyxoZIMlaAmUNjeDSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Lead Created Date:" = _t, #"Timeframe to Buy:" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Lead Created Date:", type date}}, "en-BS"),
    #"Added Start Range" = Table.AddColumn(#"Changed Type with Locale", "Start Range", each Number.From(Text.BeforeDelimiter([#"Timeframe to Buy:"], "-")) * 30.4),
    #"Added End Range" = Table.AddColumn(#"Added Start Range", "End Range", each Number.From(Text.BeforeDelimiter(Text.AfterDelimiter([#"Timeframe to Buy:"], "-"), "Months")) * 30.4),
    #"Inserted Average" = Table.AddColumn(#"Added End Range", "New Date", each Date.AddDays([#"Lead Created Date:"], Number.Round(List.Average({[Start Range], [End Range]}), 0)), type date)
in
    #"Inserted Average"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Anonymous]

Hi Ed,

The code that you've provided above for this, is this an M code which goes into a blank query? I'm just coming back to this ask and seeing how I can put this solution into practice. Thanks!

Regards,

Mehal

[edhans]

It is M code. 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.