cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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 🙂

1 ACCEPTED SOLUTION
Super User

Hi @Jumper

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

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

4 REPLIES 4
Super User

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"),
[ 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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helper I

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.)

Helper I

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 🙂

Super User

Hi @Jumper

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

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors