Add the average amount of days to a date based on each sales person average close rate

[rachaelwalker]

I am looking for a formula to add a numeric value to a date (preferably in business days). The numberic value is the average days it takes an opportunity to close for each sales person. Example, if Tom has an average Close Rate of 40 days, I would like to add 40 business days to all his new opportunity start dates to determine an expected close date. 

[JasperDJ · Accepted solution]

Hi Rachael,

I managed to get the results in power query by first grouping the average closed rate, and then using that result to calculate a new date when the closing date = null.

Working with working days is a bit more of a hassle, but it is possible. Seeing as the maximum difference can only be a few days between working with an average on working days and regular days, and you are estimating a date based on an average; I would opt for using the regular day sample. For example:

I used a random data set to achieve the following.

-Regular days-

Input:

Output:

Query:

let
Source = Excel.Workbook(File.Contents("C:\Users\Gebruiker\Desktop\Powerbi\Overig\Sample data est closed date.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Opportunity Number", "Accountmanager", "Date became lead", "Date Closed", "Close Rate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Opportunity Number", Int64.Type}, {"Accountmanager", type text}, {"Date became lead", type date}, {"Date Closed", type date}, {"Close Rate", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Duration closed", each Duration.Days([Date Closed]-[Date became lead])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Accountmanager"}, {{"Average close rate", each List.Average([Duration closed]), type nullable number}, {"All", each _, type table [Opportunity Number=nullable number, Accountmanager=nullable text, Date became lead=nullable date, Date Closed=nullable date, Close Rate=nullable number, Duration closed=nullable number]}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Average close rate", Int64.Type}}),
#"Expanded All" = Table.ExpandTableColumn(#"Changed Type1", "All", {"Opportunity Number", "Date became lead", "Date Closed", "Close Rate", "Duration closed"}, {"Opportunity Number", "Date became lead", "Date Closed", "Close Rate", "Duration closed"}),
#"Added Custom1" = Table.AddColumn(#"Expanded All", "Estimated Closed Date Based on Average", each if [Date Closed]=null and [Average close rate]<>null then 
Date.AddDays([Date became lead],[Average close rate]) else null),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Estimated Closed Date Based on Average", type date}})
in
#"Changed Type2"

-Working days-

Probably not the cleanest solution; but it works. I've used an additional table name Date Index Table. Here I removed the weekends (days 6 and 7). I did not take into account any holidays, this can be added by filtering out the relevant dates before the index column is added. I also did not take into account if a lead date takes place during the weekend and the relevant index cannot be found in the Date Index Table. Easiest solution would be to shift the Lead date to the monday since that would probably be the first time it would be picked up by the Account Manager. I usually achieve this with the following formula:

Date.ToText(Date.StartOfWeek([Date], Day.Monday),"dd/MM/yyyy"))

Query Date Index Table:

let
startDate = Number.From(Date.From("1/1/2010")),
endDate = Number.From(Date.From("31/12/2030")),
Source = {startDate..endDate},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
#"Weekday Number" = Table.AddColumn(#"Changed Type", "Weekday Number", each Date.DayOfWeek([Date], Day.Monday)+1),
#"Filtered Rows" = Table.SelectRows(#"Weekday Number", each ([Weekday Number] <> 6 and [Weekday Number] <> 7)),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type)
in
#"Added Index"

Query working days:

let
Source = Excel.Workbook(File.Contents("C:\Users\Gebruiker\Desktop\Powerbi\Overig\Sample data est closed date.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Opportunity Number", "Accountmanager", "Date became lead", "Date Closed", "Close Rate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Opportunity Number", Int64.Type}, {"Accountmanager", type text}, {"Date became lead", type date}, {"Date Closed", type date}, {"Close Rate", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Duration closed", each if [Date Closed]=null then 0 else Duration.Days([Date Closed]-[Date became lead])),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "List dates", each List.Dates([Date became lead],[Duration closed]+1,#duration(1,0,0,0))),
#"Expanded List dates" = Table.ExpandListColumn(#"Added Custom2", "List dates"),
#"Added Custom3" = Table.AddColumn(#"Expanded List dates", "Work day duration", each if Date.DayOfWeek([List dates], Day.Monday)+1=6 or Date.DayOfWeek([List dates], Day.Monday)+1=7 then 0 else 1),
#"Grouped Rows1" = Table.Group(#"Added Custom3", {"Opportunity Number", "Accountmanager", "Date became lead", "Date Closed", "Close Rate", "Duration closed"}, {{"Sum Work Days", each List.Sum([Work day duration]), type number}}),
#"Replaced Value" = Table.ReplaceValue(#"Grouped Rows1",each [Sum Work Days],each if [Date Closed] = null then null else [Sum Work Days],Replacer.ReplaceValue,{"Sum Work Days"}),
#"Grouped Rows" = Table.Group(#"Replaced Value", {"Accountmanager"}, {{"Average close rate", each List.Average([Sum Work Days]), type nullable number}, {"All", each _, type table [Opportunity Number=nullable number, Accountmanager=nullable text, Date became lead=nullable date, Date Closed=nullable date, Close Rate=nullable number, Duration closed=number, Sum Work Days=nullable number]}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Average close rate", Int64.Type}}),
#"Expanded All" = Table.ExpandTableColumn(#"Changed Type1", "All", {"Opportunity Number", "Date became lead", "Date Closed", "Close Rate", "Duration closed"}, {"Opportunity Number", "Date became lead", "Date Closed", "Close Rate", "Duration closed"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded All", {"Date became lead"}, #"Date Index Table", {"Date"}, "Date Index Table", JoinKind.LeftOuter),
#"Expanded Date Index Table" = Table.ExpandTableColumn(#"Merged Queries", "Date Index Table", {"Index"}, {"Index - Became lead"}),
#"Added Custom4" = Table.AddColumn(#"Expanded Date Index Table", "Index + average close rate", each [#"Index - Became lead"]+[Average close rate]),
#"Merged Queries1" = Table.NestedJoin(#"Added Custom4", {"Index + average close rate"}, #"Date Index Table", {"Index"}, "Date Index Table", JoinKind.LeftOuter),
#"Expanded Date Index Table1" = Table.ExpandTableColumn(#"Merged Queries1", "Date Index Table", {"Date"}, {"temp_est closed date"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Date Index Table1", "Estimated Closed Date Based on Average", each if [Date Closed]=null and [Average close rate]<>null then 
[temp_est closed date] else null),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Estimated Closed Date Based on Average", type date}})
in
#"Changed Type2"

Hope this helps 🙂

Kind regards,

Jasper

[rachaelwalker]

This was super helpful thank you! I was able to use a similar formula to what you suggested and it is returning the dates as expected. Thank you taking the time to explain this to me!!

I created a separate dataset to find the averages for each Sales person then I merged that dataset onto the Opportunity dimension table. I was concerned about permission but it appears to be fine. The formula below is what I used to calculate business days. 

if [#"Avg Days to Close - 3 Months"] <> null then List.Select( List.DateTimes( Date.AddDays([Date Became Lead], 1), [#"Avg Days to Close - 3 Months"] + 1 + ([#"Avg Days to Close - 3 Months"] / 5) * 2, #duration(1, 0, 0, 0)), each List.Contains({1..5}, Date.DayOfWeek(_))){[#"Avg Days to Close - 3 Months"] - 1} else null

[parry2k]

@rachaelwalker do you want to calculate the average days based on all the closed oppy for each sales person, and apply that average to the open oppy, correct?

Do you have sales person dimension in your model?

[rachaelwalker]

@parry2k I have added a Sales Person table. It has two columns: Member ID and Account Manager. The member ID is also in my opportunity facts table

[rachaelwalker]

@parry2k That is correct. Currently, I do not have the Sales Person dimension table in my pbix but I can add it