Forum Discussion

AlexDawson1111's avatar
AlexDawson1111
New Member
3 years ago

Calculate Hours Per Week

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

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      AlexDawson1111
      New Member
      3 years ago

      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