topic Re: Available time for each worker in DAX Commands and Tips

Available time for each worker

Anonymous — Wed, 29 Jul 2020 08:34:35 GMT

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.

Re: Available time for each worker

lbendlin — Wed, 29 Jul 2020 13:20:54 GMT

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

Re: Available time for each worker

Anonymous — Wed, 29 Jul 2020 15:25:32 GMT

"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?

Re: Available time for each worker

Anonymous — Thu, 30 Jul 2020 07:07:11 GMT

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.

Re: Available time for each worker

Anonymous — Thu, 30 Jul 2020 08:32:21 GMT

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.

Re: Available time for each worker

Anonymous — Thu, 30 Jul 2020 09:04:23 GMT

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.

Re: Available time for each worker

Anonymous — Thu, 30 Jul 2020 09:32:21 GMT

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.

Re: Available time for each worker

Anonymous — Thu, 30 Jul 2020 09:46:19 GMT

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.

Re: Available time for each worker

Anonymous — Thu, 30 Jul 2020 11:01:14 GMT

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.

Re: Available time for each worker

Anonymous — Thu, 30 Jul 2020 11:24:26 GMT

Thank you for trying Anonymous

Re: Available time for each worker

sturlaws — Tue, 04 Aug 2020 12:44:40 GMT

Hi Anonymous

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.

Re: Available time for each worker

Anonymous — Thu, 20 Aug 2020 09:40:29 GMT

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