cancel
Showing results for
Did you mean:
Helper III

## Hours Allocation per day based on start and end dates in a row

Hello all,

I'm new to Power BI Desktop, so please excuse the naiveté

I am looking to create a Matrix visual from a dynamicly fed (Smartsheet report) table with a date in a Start column and a date in a Finish column, as well as an allocation of hours in a 3rd column.  The Matrix is intended to show how the hours allocation is broken out by day between Start an End date.  Please see examples:

This is how the data is fed to Power BI.

The idea of the Matrix is below.

With the Start date of 9/28 and the Finish date 9/29, the idea is for that 11 hours allocation in the first row to divide evenly based on the number of work days in between, and place those numbers into the matrix.  in this case,  9/28 = 5.5 & 9/29 = 5.5.  Should that be 4 days long, each day would show that allocation as 2.75 per day.

Any help anyone has to accomplish this would be greatly appreciated.

Thanks!
TO

18 REPLIES 18
Helper III

My apologies, I had only used visual references to illustrate my need, i had not thought about the accuracy of the data.  Lets try again!

I am looking to create a Matrix visual from a dynamicaly fed (Smartsheet report) table with a date in a Start column and a date in a Finish column, as well as an allocation of hours in a 3rd column.  The Matrix is intended to show how the hours allocation is broken out by day between Start an End date.  Please see examples:

This is how the data will be fed to Power BI via Smartsheet report.

The idea of the Matrix is below.

For example: For Employee 2 whose task has a Start date of 9/28 and the Finish date 10/2, the idea is for that 4 hours allocation in the first row to divide evenly based on the number of work days in between, and place those numbers into the matrix.  in this case,  9/28 = 1.33, 9/29 = 1.33 & 10/2 = 1.33.

Any help anyone has to accomplish this would be greatly appreciated.

Thanks!
TO

Super User

Hi,

You may refer to my solution here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III
Thank you Ashish, but as stated in my original post, I am trying to show this as a Matrix in Power BI, not excel.
Super User

Hi,

I just imported the Excel model to POwerBI desktop.  Check the PowerBI desktop file here.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

Hi Ashish,

this looks great, but i am struggling to see how you came to this.  Can you elaborate a bit more on how you arrived at your solution?

How does is this table created from the Smartsheet import as mentioned in my original post?

im sorry for all the questions, i am trying to learn as well as searching for a solution

Super User

Hi,

This is a 2 step process.  Using Power Query, i first created one row for each date.  This was accomplished with the M language in Power Query.  Thereafter, in Power Query itself, i extracted the Day of the week.  I then loaded the data to the Data Model (PowerPivot) and wrote a caluclated field formula there to split the hours into the number of weekdays that each employee worked on.  Thereafter, built the Matrix visual.

Click on Home > Edit Queries and you will be able to see all the steps i performed in Power Query.  Click on Close and Apply.  Click on Data on the left hand side pane and in the right hand side pane click on Allocated hours to see the calculated column formula i wrote.

Thereaftet, i built the matric visual.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

i think i am beginning to understand, however, i am unable to see much in the Query Editor.  When I click "edit query", it shows me errors.  see attached shots.   I am unable to work backwards from this to understnad how it was put together.  THank you for trying though!

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

This looks excellent, and I am very close to understanding how you came to this!

I am going to test this over the next day or two, and make sure I don't come across anything funky as the data updates.

Thank you so much for your help, it is much appreciated!!

Helper III

I have a found a bug, though i believe its due to my lack of explaination.

When the same Employee is assigned multiple tasks, the math for those tasks hours is not correct.  It seems to be calculating ALL of the hours assigned to a person, then reducing it for some reason.

Example.

Employee 1 9/28 - 10/2 20 hours

Employee 1 10/04 1 10/06 - 2.5 hours

Employee 1 10/6 - 10/10 1 hours

On the matrix, 9/28 shows only 2.22 hour allocated.   Given this is task specific, 3 days of 2.22 hours does not equal 20.

I hope I have explained this well.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

i think i am beginning to understand, however, i am unable to see much in the Query Editor.  When I click "edit query", it shows me errors.  see attached shots.   I am unable to work backwards from this to understnad how it was put together.  THank you for trying though!

Community Champion

Hi @TO_CB

Can you let us know what if the dates falls between 09/28/2017 to 10/10/2017.   How do you want to show the result.  There are 2 sets of Satudays and Sundays in between.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Helper III
In this case, weekdays only. Weekends should be excluded if possible.
Community Champion

Hi @TO_CB,

Let me rephrase the question.

1. Date range is 09/28/2017 to 10/10/2017

2. There are three weeks coming in this.

i)  09/28 and 09/29

ii)   02/10/  to  06/10

iii)   09/10  to  10/10

3. The number of Days total , excluding Sat and Sun is  - 9.

4. how do you want to show the result

is it 09/28 - 1/9, 09/29 - 1/9, 02/10 - 1/9 etc.

or 09/28 - 1/2, 09/29- 1/2 , 02/10- 1/5,03/10-1/5,  09/10 -1/2 , 10/10 - 1/2

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Helper III
Hello again! If I understand correctly, the second option.
Super User

Hi,

Please share your data and more importantly show the expected result for the data that you share.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Memorable Member

it is unclear as to what data set contains the value 11 and if there is a field in that data set that will link it to the record you display in your first data set example that contains the 2 dates and the 4 value.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!