cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mehal1996
Helper II
Helper II

Use Created Date Column to form a Predicted Close Date Column

Hi there,

 

I'm looking to take 2 columns in my data source to then create a 3rd column which will be the Predicted Close Date Column. The 2 columns mentioned are shown in the example data below.

 

Is it possible to average the Timeframe to buy column and then use that averaged number to add onto the Lead Created Date to form a new Predicted Close Date Column? Thanks.

 

Lead Created Date:Timeframe to Buy:
07/06/20213 - 6 Months
13/06/2021

6 - 9 Months

20/06/20219 - 12 Months
1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

Yes @Mehal1996 - 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:

edhans_0-1623080501135.png

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.



 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

1 REPLY 1
edhans
Super User III
Super User III

Yes @Mehal1996 - 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:

edhans_0-1623080501135.png

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.



 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors