Spread value across time between 2 dates

[jen8080]

Hello-

I need to be able to determine the number of months betwen the start and end date and show how many employees I need to per month to fill these requested hours. The requested hours should be distrbuted evenly across the start and end dates. 

Each employee is expected to work 50 hours lets say. 

Here is just a sample of data to help:

Project ID	Record Entry	Requested Hours	Work End Date	Work Start Date
Project 1	14466	4	Wednesday, December 23, 2020	Wednesday, December 16, 2020
Project 1	14466	20	Wednesday, December 23, 2020	Wednesday, December 09, 2020
Project 2	13990	12	Monday, August 31, 2020	Wednesday, June 10, 2020
Project 2	13990	50	Monday, August 31, 2020	Wednesday, June 10, 2020
Project 2	13990	175	Monday, August 31, 2020	Wednesday, June 10, 2020
Project 2	13990	692	Monday, August 31, 2020	Wednesday, June 10, 2020
Project 2	13990	40	Monday, August 31, 2020	Wednesday, June 10, 2020
Project 2	13990	125	Monday, August 31, 2020	Wednesday, June 10, 2020
Project 2	13990	20	Monday, August 31, 2020	Wednesday, June 10, 2020
Project 3	13586	10	Thursday, March 31, 2022	Tuesday, March 10, 2020
Project 4	14125	1	Monday, August 31, 2020	Friday, July 31, 2020
Project 4	14125	6	Monday, August 31, 2020	Monday, July 27, 2020
Project 4	14125	6	Monday, August 31, 2020	Monday, July 27, 2020
Project 4	14125	4	Monday, August 31, 2020	Monday, July 27, 2020
Project 4	14561	19	Friday, February 26, 2021	Monday, February 08, 2021
Project 4	14258	14.5	Friday, January 08, 2021	Thursday, October 01, 2020
Project 4	14258	25	Friday, January 08, 2021	Thursday, October 01, 2020
Project 4	14258	53.5	Friday, January 08, 2021	Thursday, October 01, 2020
Project 4	14258	18	Friday, January 08, 2021	Thursday, October 01, 2020
Project 4	14258	18	Friday, January 08, 2021	Thursday, October 01, 2020
Project 4	14561	19	Friday, February 26, 2021	Monday, February 08, 2021
Project 4	14561	66.5	Friday, February 26, 2021	Monday, February 08, 2021

[Ashish_Mathur]

Hi,

1. Why are there multiple rows for the same Project ID and Record Entry combination?
2. As an example, for the first 2 rows, how would you calculate the number of months?
3. Also, for project 4 and record entry 14258, how would you calculate the number of months?

[jen8080]

1. This is just a sample sample of a much larger confidential dataset. Each study can have several requests for more hours to be added.

2.Again small  sample of the acutal dataset so we could use days if months aren't possible.

3. above is also applicable for this.

[v-shex-msft]

Hi @jen8080,

I still not so clear for your scenario, can you please share more details about these?

How to Get Your Question Answered Quickly 
#1, Did these request hours means the daily works hour that assigns in the date ranges?

#2, For the records with the same 'project id' and 'Record Entry', did these need to be summarized or calculate standalone?

#3, Since you are talking about working hours, how the weekend(workday range) configured in your date range?

Regards,
Xiaoxin Sheng

[v-shex-msft]

Hi @jen8080,

It seems like a common multiple date range analysis requirement, please take a look at the following links if they meet your scenario:

Spread revenue across period based on start and end date, slice and dase this using different dates 

Before You Post, Read This#“start date” and “end date” 

Regards,

Xiaoxin Sheng

[xMikeDx]

your time req in months is not going to fly when you are looking at partial months especially at both ends.

i believe you need to use datediff to get the count of days filter out any non workdays then determin how many hours per day each row is projected to meet your est. 50.

this is psudeo code.

measure =: 

var __hours = 50

var __dayrange = calculate(datediff (facttable[start_date], facttable[end_date], Day),'datetable'[isweekend]=false)

var __result = divide( __dayrange, __hours)

return __result

something like this