Create Hit Rate range with first quoted date and PO Date

[gunther15]

Hi I was wondering what logic I need to build in to create a hit rate range for this:

Days to First Order (days between FirstQuotedDate and BrandFirstOrderedDate):
1 - 91: 0 to 3 Months
92 - 182: 3 to 6 Months
183 - 273: 6 to 9 Months
274 - 364: 9 to 12 Months
365+: 12 Months or older

Here is an example of some of the data I have:

The PO date is the brand first ordered date in this case.  I was wondering how what logic I need to create that hit rate range from the data I have above.

Thank you

[JamesRobson]

Hi,

 I would start with a helper column to get the age

= Table.AddColumn(#"Changed Type1", "Age", each Duration.Days(Date.From([First Quoted Date]) - Date.From([PO Date])))

then a column to label the category

= Table.AddColumn(#"Added Age", "Category", each if [Age] < 92 then "0 to 3 Months"
else if [Age] < 183 then "3 to 6 Months"
else if [Age] < 274 then "6 to 9 Months"
else if [Age] < 365 then "9 to 12 Months"
else "12 Months or Older")

Whole query below

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"PO Date", type date}, {"First Quoted Date", type date}}),
#"Added Age" = Table.AddColumn(#"Changed Type1", "Age", each Duration.Days(Date.From([First Quoted Date]) - Date.From([PO Date]))),
#"Added Category" = Table.AddColumn(#"Added Age", "Category", each if [Age] < 92 then "0 to 3 Months"
else if [Age] < 183 then "3 to 6 Months"
else if [Age] < 274 then "6 to 9 Months"
else if [Age] < 365 then "9 to 12 Months"
else "12 Months or Older")
in
#"Added Category"

There probably is a clever way to do it in one step but I'm not that good 🙂 

Thanks,