cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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.

Screen Shot 2017-09-22 at 10.04.13 AM.png

 

The idea of the Matrix is below. 

matrix.png 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
Highlighted
Memorable Member
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.

 

www.CahabaData.com
Highlighted
Super User III
Super User III

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
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper II
Helper II

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.

SmartsheetData.png

 

The idea of the Matrix is below.

 FinishedData Example.png

 

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

Highlighted

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!
Highlighted

In this case, weekdays only. Weekends should be excluded if possible.
Highlighted

Hi,

 

You may refer to my solution here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

Thank you Ashish, but as stated in my original post, I am trying to show this as a Matrix in Power BI, not excel.
Highlighted

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!
Highlighted

Hello again! If I understand correctly, the second option.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors