How do i translate working days into hours, which can be updated more than once a day, in Power BI?

Question

My first time ever using Power BI, and i have been tasked with comparing our ticket response time to what we have promised our customers. I need to show how many working days and workings hours have gone by, since a ticket was created. Business Hours could be 08:00 - 16:00 (it varies denpending on customer), and working days is Monday, Tuesday, Wedensday, Thursday and Friday. Making a working day 8 hours. If a ticket comes in at 15:00, and we have 1 working day to respond, then we would need need to respond by 15:00 the following day, not 23:00 at the same day. My issue as of right now, is that my current calculations is in full work days. Meaning that my Working Hours Elapsed is only being updated with 8 hour intervals. This isn't very useful on a daily basis, in the cases where we only have a single day to respond to a ticket, and need to be made aware that it is nearing a deadline.

In practice this translates into a column where a ticket created today, would have 0 Working Days Elapsed and 0 Working Hours Elapsed, even if the current time is 15:00, and the ticket was created at 08:00.

At the moment i have (among others) these two columns: Working Days Elapsed (WDE) and Working Hours Elapsed (WHE).

WDE is calculated the following way, to exclude weekends:

(InitialDate as date) as number => 
let
    FinalDate =  DateTime.Date(DateTime.LocalNow()),
    DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
    DaysList = List.Dates(List.Min({InitialDate,FinalDate}),DaysBetweenDates, Duration.From(1)),
    WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
    WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
in
    WorkingDays

Work Hours Elapsed, is at the moment a bit more simple:

Work Hours Elapsed = 'Work Items'[Work Days Elapsed]*'Work Items'[Business Hours]

How would i convert this into something, which could be refreshed at least every hour? Maybe even more often.

I hope i have provided enough information, and that it all makes sense.

@Jonee No, but if it can solve my issue i'm open to the idea. — Anders Jensen
My assumption about such tables is that they converts the dates into a setup where weekends (and holidays etc) are ignored, thus giving you a clean numerical sequence for you to calculate differences in "working days". The same for times where it converts your "working hours" into a normalized number (a fractional amount of a "day" probably?). I think that is the kind of thing you're supposed to setup with relational tables in BI though. — Wedge

Anders Jensen Anders Jensen · Accepted Answer · 2020-01-30T11:56:20

I figured it out, it was mostly a question of math. I made a solution which can be updated hourly, which seems to be fine.

I added two columns: Business Hours Start and Business Hours End. Then converted my WorkDaysElapsed into decimals the following way (I have tried giving it some meaningful names):

HowFarIntoTheDay = now - start

HowManyPercentageOfTheToalDayHasGoneBy = HowFarIntoTheDay/(end-start)

WorkDaysElapsedInDecimals = WorkDaysElapsed + HowManyPercentageOfTheToalDayHasGoneBy

I don't know if this is the best possible solution, but it seems to do the job.

How do i translate working days into hours, which can be updated more than once a day, in Power BI?

1 Answers