Forum Discussion

sakhan1021's avatar
sakhan1021
Regular Visitor
8 years ago

Splitting Values between months of two dates

 

I am struggling with how to show a month by month resource utilization based on assigned project.  

 

let's say i have two tabs in an excel spreadsheet - projects and projectallocations

 

projects:

projectid, projectname, project est hours, project est days, start date, end date

1 --------- project a ----- 500 ---------------- nn ------------------ 1/3/2019 - 5/30/2019

2 --------- project b ----- 200 ---------------- nn ------------------ 3/15/2019 - 5/15/2019

 

projectallocations:

projectid, projectname, resource, est hours, fte (year) allocation

1---------- project a ---- joe a ----- 300 ------ 16%

2 --------- project b ---- joe a ----- 200 ------ 11%

 

dates:

date

 

I followed this post "splitting values between two dates" to 'spread' the est hours across project duration - e.g. project b split 100 hours in march and april and so on.

 

Now, when I put a matrix on the page I get this.... 

 

Month | Project A | Project B | Total

Jan ----- 100 ------- blank ----- 200

Feb ----- 100 ------- blank ----- 200

March -- 100 ------- 100 ------- 200

April ---- 100 ------- 100 ------- 200

May ---- 100 ------- blank ----- 200

June ---- 100 ------- blank ----- 200

July ---- 100 ------- blank ----- 200

Aug ---- 100 ------- blank ----- 200

Sept ---- 100 ------- blank ----- 200

Oct ---- 100 ------- blank ----- 200

Nov ---- 100 ------- blank ----- 200

Dec ---- 100 ------- blank ----- 200

 

What I need is the total to sum by month for what was scheduled/allocated..

month by month (following the above format)

e.g. Jan and Feb = 100, Mar and Apr = 200, May - Dec = 100.  

 

 

I hope this is making sense.  

 

 

 

 

 

Need Help

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    8 years ago
    According to your description, we can't reappear what's in your matrix ,
    for example,project b 100 in march and april  but it will start on 3/15/2019 and end on 5/15/2019 ,where is May?
    project a is allocated only 300 hours and end on 5/30/2019,why it still allocated from june to Dec?
     
    Please show us more details and you can share pbix or some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
     
     
    Best Regards,
    Lin