Estimated work capacity pr day based on FROM and TO dates

[Sindre]

Need some help with creating a table.

For resource planning, I need to calculate the available capacity for each resource pr day. The estimated hours should be spread as average over the scheduled periode where available WorkHours>0.

Taskid	Resource	ScheduleStartDate	ScheduleEndDate	EstimatedHours
100	PersonA	02.01.2020	06.01.2020	6
101	PersonA	02.01.2020	03.01.2020	10
102	PersonB	02.01.2020	03.01.2020	8

Timetable

WorkDate	WorkHours
02.01.2020	7
03.01.2020	7
04.01.2020	0
05.01.2020	0
06.01.2020	7

Result table

Date	Taskid	Resource	EstAvgHours
02.01.2020	100	PersonA	2
03.01.2020	100	PersonA	2
06.01.2020	100	PersonA	2
02.01.2020	101	PersonA	5
03.01.2020	101	PersonA	5
02.01.2020	102	PersonB	4
03.01.2020	102	PersonB	4

This result table will be good for different summations and visualizations.

Regards Sindre

[V-lianl-msft]

Hi @Sindre ,

Unfortunately, it's hard to achieve exactly the same table you want, and the two tables you provide can't establish the right relationship.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

You should try a formula like this

Current Employees = CALCULATE(AVERAGEX(FILTER(TASK,TASK[ScheduleStartDate]<=max('Date'[Date]) && (ISBLANK(TASK[ScheduleEndDate]) 
 || TASK[ScheduleEndDate]>max('Date'[Date]))),(TASK[EstimatedHours]/datediff(ScheduleStartDate,ScheduleEndDate, DAY))),CROSSFILTER(TASK[ScheduleStartDate],'Date'[Date],None))

In case this did work create it a column first

(TASK[EstimatedHours]/datediff(ScheduleStartDate,ScheduleEndDate, DAY))

when you take task id and make it ungrouped it will show the required results

Referred to this article on similar problem : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Sindre]

I got this formula to work, and it is sort of usable. This will only work on a day by day graph, and unfortunately not by f.ex week. Not sure how I should handle that. But thanks for the resolution.