Available time for each worker

[nuldum]

Hi all,

I'm fairly new to Power BI and DAX, and I've already learnt alot from this community - thank you!

I'm trying to create an overview of the time available for each worker in my department. The purpose of this is to quickly find a worker who has time for urgent cases.

I have a data set with Worker ID, Task ID, Deadline of the task, and days it takes to perform the task.
Furthermore I have calculated a temporary start and end date, and performed a check to see if any tasks overlap.

What I want to do is calculate an adjusted start and end date so the tasks don't overlap without exceeding the deadline.
I would also like to visualize this is in meaningful way so I can spot if a worker has available time for a urgent deadline.

I have calculated my columns as follows:

startDay:

startday = [endDay] - [Days]

endDay:

endDay = [deadline]

Overlap:

Overlap = 

var __worker = [Worker ID]

var __overlap = COUNTROWS(
    FILTER(ALL('data'); [Worker ID] = __worker && EARLIER(data[endDay]) > [startDay] && EARLIER('data'[endDay]) <= [endDay])
)-1

return
IF(
    __overlap=-1;
    0;
    __overlap
)

The data set is available here: https://1drv.ms/x/s!AlGwu4CgVOGIhYc-8wFK5o4uj3e_Eg?e=crjUfG

The pbix file is available here: https://1drv.ms/u/s!AlGwu4CgVOGIhYc9ip4krLoTuYolgg?e=GuTygg

Thank you in advance.

[Anonymous]

"What I want to do is calculate an adjusted start and end date so the tasks don't overlap without exceeding the deadline."

This is not clear. What's the algorithm for this calculation?

[nuldum]

The algorithm should be something like:

SWITCH([Overlap],
	5, adjustedEndDay=endDay,
	4, adjustedEndDay=(startday-1 of the above task),
	3, adjustedEndDay=(startday-1 of the above task),
	2, adjustedEndDay=(startday-1 of the above task),
	1, adjustedEndDay=(startday-1 of the above task),
	DATE(1900,1,1)

In the above example it can only handle 5 overlaps and I'm not sure if there is any way to find "startday of the above task".

As @lbendlin pointed out above, I'm using this tool for something it's not built for. However, I was hoping to reduce the number of tools we are using. Right now we are doing the process management in Excel, and it's a pretty messy document with a lot of macros. It breaks easily and we spend a lot of time debugging it.

[Anonymous]

Hi there.

Would you care to paste an Excel screenshot of some simple example of the above code in action, please? I mean I'd like to see this algorithm working in reality. Just a very simple example if you don't mind.

Thanks.

[nuldum]

@Anonymous 
I have a stripped down version of the excel document which we use now (I have removed most parts because the contain critical data, so the macros doesn't work anymore) - Also sorry for any leftover Danish in the document.

I have taken a screenshot of the current Excel document so you don't have to download it:

As you can see we have a list of task, with a deadline, how long it takes to perform the task, a start day, and end day, a buffer (this particular worker is way behind schedule) and remaining days from today to deadline.

This document does not use the algorithm is proposed above.

I hope this helps visualizing what I'm trying to achieve.

[Anonymous]

OK. That's good. One last thing we need here... would you add the column(s) that results from your algorithm? I'd like to see the output of your calculation. You will, of course, do it manually. Just put the right output of the calc on the spreadsheet and paste here. Thanks.

By the way, I can't download files due to my company's policy.

[nuldum]

I'm not entirely sure what you are asking for. The screenshot in the previous post is what we use today, and is what I'm trying to achieve in Power BI.

The raw data is Worker ID, Task No., Deadline, and duration.

The output columns in the Excel-file, which we use today, is Start, End, and Buffer.

It's fairly simple, I'm just not sure how well Power BI can compare rows and perform a similar task.

[Anonymous]

Sorry, I don't get it. Normally, you'd present data you start with and data you want to end up with and a good description of what the transformation is. From what you've pasted I can't even quickly figure out what the two stages are. Sorry. Maybe someone else will be able to help you.

[nuldum]

Thank you for trying @Anonymous 

[sturlaws · Accepted solution]

Hi @nuldum 

I have taken a look at your case, and I think it is doable. 

1. Open power query, and sort the table by Worker ID, Deadline, Task ID and Days, in that order. It should look like this(notice the small numbers next to the sort symbol):

2.  add an index column in power query, rename it to e.g. index, close and apply your changes

3.  Create your [New Start day]-column like this:

new start day = 
VAR _worker = [Worker ID]
VAR _endDay = [endDay]
VAR _startDay = [startDay]
VAR _task = data[Task ID]
VAR _index = data[Index]
VAR _overlappingTasks =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( data, [Task ID], data[startDay], data[endDay], data[Index] ),
            "numberOfOverlappingDays", DATEDIFF ( data[startDay], _endDay, DAY )
        ),
        FILTER (
            ALL ( 'data' ),
            [Worker ID] = _worker
                && [startDay] < _endDay
                && _task <> [Task ID]
                && _index < data[Index]
        )
    )
VAR _numberOfOverlappingDays =
    SUMX ( _overlappingTasks, [numberOfOverlappingDays] )
RETURN
    data[startDay] - _numberOfOverlappingDays

4. In order to display which workers who are available for a new task, first create a table of dates like this

Dates = CALENDAR(DATE(2019,1,1),DATE(YEAR(TODAY())+1,6,30)) 

5. Then create a table with the number of days a duration can last(rename the column from [value] to [duration]):

Duration = GENERATESERIES(1,15)

6. Create a measure like this:

Available workers = 
VAR _start =
    CALCULATE ( MAX ( Dates[Date] ) )
VAR _duration =
    CALCULATE ( MAX ( 'Duration'[Task duration] ) )
VAR _end = _start + _duration
VAR _worker =
    CALCULATE ( SELECTEDVALUE ( data[Worker ID] ) )
RETURN
    COUNTROWS (
        FILTER (
            data,
            ( _start <= data[new start day]
                && _end > data[new start day] )
                || ( _start < data[new end day]
                && _end >= data[new end day] )
                || ( _start <= data[new start day]
                && _end >= data[new end day] )
        )
    ) + 0

6. Create a table in the report with the worker ids, and use [Available workers] as a filter for this table(in the filter pane of the visual). If  [Available workers] returns 0 then the worker is available

7. Add slicers to the report page for the Date and Duration(using the columns from the tables created in step 4 and 5.

I have attached the updated report

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[nuldum]

Hi @sturlaws 

Sorry for the long delay in my reply (summer vacation).

Thank you so much for your solution. This was exactly what I was looking for. I even added a "as Timeline"-visualization to really visualize time available for each worker.

I didn't know I could sort the columns in a hierarchy in PowerQuery - this is really going to save my day in my future as well.

Again thank you!

~nuldum

[lbendlin]

You are using a reporting tool for process management.  Keep in mind that Power BI cannot write back (yet) unless you use mcguyver workarounds.