Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

28 REPLIES 28
JessSvetlik
Frequent Visitor

Hello, 

 

I worked through the examples that were provided, and I was looking for help when Saturday and Sunday are considered working days and need allocation.  

 

Thank you!

Hi,

Share some data, describe the question and show the expected result.


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

I was using the same concept of allocating hours by employee, but I am looking to use this for monthly cost.  For instance, I have a outage for 45 days that spreads across 2 months for a total dollar value.  I am looking for a formula that will allocate the costs across the months.  See data below. 

 

2022-09-16_7-11-20.jpg

Shouldn't days be calculated as Finish - Start + 1?  Share data in a format that can be pasted in an MS Excel file.


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

I know how long a project will last and when we plan to start the project, which then calculates the finish date.  I have not allocated in excel, but I used the original post's methodolgy to build in Power BI.  But I am looking to see if there is a higher level formula that takes the start date and allocates based on number of days without paying attention to the day of the week (Saturday/Sunday).   Please see below, but that is based on excluding Saturday & Sunday.  I am looking at a higher level than excluding Saturday & Sunday.

 

2022-09-19_7-43-57.jpg

Your response does not answer the question i asked. 


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

The information I have is an anticiapted start date and the duration of a job in days.  My formula for finish date is start date + # of days.  I am wanting to have the cost of the job spread across the days that the job will be worked without needing to exclude any days of the week.  

Hi,

Share data in a format that can be pasted in an MS Excel file.


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

Please let me know if this works.

 

JobAccountAmountStartFinishDays
Job 1Services $          100,0001/1/20221/31/202230
Job 2Services $            20,0002/5/20223/22/202245
Job 3Rental $            20,0003/5/20225/19/202275
Job 4Material $            50,0007/25/202211/2/2022100
Job 5Catalyst $      1,500,00012/15/20225/14/2023150
Job 6Rental $          200,0001/1/202212/31/2022364
Job 7Rental $          150,0001/5/20223/21/202275
Job 8Catalyst $          256,0006/5/20226/28/202223
Job 9Services $      2,350,0003/5/20225/12/202268
Job 10Services $      5,236,0008/5/202211/3/202290
Job 1Rental $          100,0001/1/20221/31/202230
Job 4Services $            50,0007/25/20229/13/202250

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Hi,

 

You may refer to my solution here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

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
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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!

 

Added Column.pngdateerror.png

Hi,

 

Download the file again.  It will work fine.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

 

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.