Calculate Hours Per Week

[AlexDawson1111]

Hi,

I am looking for the best way to calculate hours required per week by week end date. The scenario is as follows:-

1) I have a Task Number, Task Start Date and a Task End Date. The task has effort hours. I want to calculate the hours of effort required per week (by week end date).

For example task number 1 requires 1000 hours effort. Per week end date how many hours do i need per week for this task if the start date is 01/01/2022 and the task end date is 01/03/2022. The week end date will always be a sunday.

Any help would be apprechiated.

Thanks

[amitchandak]

@AlexDawson1111 , a new table (Assumed you have column like task amount and task id)

var _Tab =
filter(

Addcolumns(generate(Task, calendar([Start Date], [End Date]) ), "Week end", [Date]+ 7-1*WEEKDAY([Date],2) -1), [End Date] =[Date])
return
Addcolumns(_tab, "Amount", [Task Amount]/ countx(filter(_tab, [Task id] = earlier([Task id])),[Task id]) )

[AlexDawson1111]

Hi,

Thanks for your assistance.

Here is a data example:-

Fact_Table Data containing the Start Date and End Date and Hours

Fact_Table

dim_Calender - This contains the date and calender ID, each task has a related calender as each task has different working days and hours etc

dim_Calendar

model:-

data model