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

[Anonymous]

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

[Ashish_Mathur]

Hi,

Please download the revised file from the same link shared above.  It is working fine now.

[Anonymous]

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!

[CheenuSing]

Hi @Anonymous

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

[Anonymous]

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

[CheenuSing]

Hi @Anonymous,

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

[Anonymous]

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

[Ashish_Mathur]

Hi,

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

[CahabaData]

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.