Yes / No indicator to track a two working day reponse metric?

[Jumper]

Our company has a promise that it will take no more than two (working days) to respond to a customer.

I'm trying to build a Yes/No indicator to see how frequently we hit or miss this target.

The formula below is on track for what i'm chasing:
Timeframe = Duration.Days([Response Date] - [Customer Contact Date]) - 2
I can then add another column
= if [Timeframe] <=2, then "Yes" else "No"
This is awfully close, but it doesn't take into account if the customer makes contact on a Friday, we have until Tuesday to respond.   I can't figure out how to integrate this component into the equation.

Our data engineers have set up a weekday indicator (= Y for weekday, = N for weekend).
They've passed a few attempted formulas to me, but none of them seem to track faithfully to the metric conditions set out above.
I rarely have the need to use Power Query, so very much learning as I go.

Any ideas on a solution for this?  Any help would be most appreciated 🙂

[dufoq3]

Hi @Jumper, this query takes into account weekends and holidays

Edit TableHoliday and TableData

let
    TableHoliday = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVPNTsJAEH6VSU+akEoL+HNUUAOCGH9jCIdpO9KNZZdsdzXefA1fzydxCoWWWkKy2d3D9zezs5OJ47nAy2/6bafhPLkP7lFvPHL5fkuf8Eqof79/UujhlzNtTJzjXejLhVjEKL82SK9ThfIxQm2EhKE1MWm4EXLWgMHK4UJoE0c52d9KlVuwMqg3GFsNQ4yW9yHCeWJwpjEUuGKe1TD7EsZBSvoDZUgZb2BRwh0GiYKe5Uy0VaTHGn41+gumMec1StbEPSkT1nHVXEgRslFfRrQg3jL3tUkWtFXlXCsVwZUWUQnU3lvNEAOlC2EO0/nXeJorLTDZoFrNMipX7iq9sCl0Yy1SIza9OC6r8T6wkgyRNDHcohFKsm5tjRz9pJqEj3qwxzanu5t4T6lRemlXlOrCWY2+W+1Iu4xbj7fR6MJDtvUIEkxhRDqkiEpzwDyvWTX41/6uSuw8sEseHIxDowIebs838eG6C97+kSwq7SrJ3Tc2K9VdBfG2JPIgz/wIGmWR1z+tgz3yt3xPZ+KDp7eAZr9za2SPcvjq8eeYy07/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, Description = _t]),
    HolidayChangedTypeSK = Table.TransformColumnTypes(TableHoliday,{{"Date", type date}}, "sk-SK"),
    HolidayBuffer = List.Buffer(HolidayChangedTypeSK[Date]),
    TableData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzDUNzIwMlHSUTIwh3NidaKVDM2Q5IwMUeWA+oxgcoYWcA5IzshY38AYrg9opjlCDmSfOdw+S4RcLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contact Date" = _t, #"Response Date" = _t]),
    DataChangedTypeSK = Table.TransformColumnTypes(TableData,{{"Contact Date", type date}, {"Response Date", type date}}, "sk-SK"),
    Ad_ResponseOK = Table.AddColumn(DataChangedTypeSK, "Response OK", each 
        [ a = List.Dates([Contact Date], 6, #duration(1,0,0,0)),
          b = List.Select(a, (x)=> not List.Contains({5, 6}, Date.DayOfWeek(x, Day.Monday))), //removed weekends
          c = List.Difference(b, HolidayBuffer), //removed holidays
          d = if [Response Date] <= c{2} then "Yes" else "No"
        ][d], type text )
in
    Ad_ResponseOK

[Jumper]

Brilliant!  I'll give this a try tomorrow.  I've got it working as deisred thanks to Phil's prior suggestion, but this is obviously the next step up (I do need to account for PHs, but thought too hard.)

[Jumper]

Thanks a lot, Phil 🙂
It's not working straight away (maybe a data type issue), but your logic makes total sense - it's the first time i've ever seen that DayOfWeekName query - that would appear to be the perfect query element in this case 🙂

[PhilipTreacy · Accepted solution]

Hi @Jumper 

Download example Excel file

You can check the number of days to respond with this code

let _Days = Number.From([Response Date] - [Customer Contact Date]) in 

if Date.DayOfWeekName([Customer Contact Date]) = "Thursday" or Date.DayOfWeekName([Customer Contact Date]) = "Friday" then _Days - 2

else _Days

Then create your Yes/No column based on those numbers

Regards

Phil