Forum Discussion

gunther15's avatar
gunther15
Frequent Visitor
3 years ago

Create Hit Rate range with first quoted date and PO Date

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

 

 

Need Help
Show and Tell

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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,