Forum Discussion

maxkhan's avatar
maxkhan
Frequent Visitor
1 year ago
Solved

date/time addition

Hi Team, I have been given a project to work on that calculates the originial planned duration of a task and the actual time it took to complete the task.   For instance if a task n is planned to t...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi maxkhan ,
    Thank you for your response. Below is a detailed overview for your reference.
    Add a New Column for the Actual Start Time of the Task

    • Open Power BI Desktop and load your dataset,In the Power Query Editor go to the Add Column tab
    • In dialog box, givea name to the new column . For example, enter ActualStartTime as the column name.
    • In the formula area,enter the below code

    = [ShiftStartTime] + #duration(0, 0, [ActualStartTimeOffset], 0)

     

    Add a New Column for the Actual Duration of the Task

    • Again, go to the Add Column tab. Click on Custom Column, name new column as ActualDuration.
    • Use the following formula to calculate the duration in hours

    = Duration.TotalHours([ActualEndTime] - [ActualStartTime])

     

    Convert the Duration to an Integer Value

    •  Go to the Add Column tab. Click on Custom Column.
    • Name the new column as Duration In Integer.
    • Use the below query

    = Number.RoundDown([ActualDuration])

    Hope the steps provided will be helpful in guiding you to create custom columns.If so,

    please consider marking Accept as solution to assist other members in finding it more easily.

    If you continue to face issues, feel free to reach out to us for further assistance!

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi maxkhan,
Thank you for reaching out to the Microsoft Fabric Community Forum regarding your issue.

In addition to the helpful response provided by Omid_Motamedise  ,I kindly suggest adding a custom column using the following query.This may help you obtain the task hours as an integer value:
= Number.RoundDown([ActualDuration])

Sharing the output screenshot below for your reference
 

 

Hope this post helps you. If so, kindly consider accepting it as the solution so that it can assist other members and help them find the answer more quickly.

 





maxkhan's avatar
maxkhan
Frequent Visitor
1 year ago

Can you show how you made this using all formulaes etc M query and steps please cheers?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi maxkhan ,
    Thank you for your response. Below is a detailed overview for your reference.
    Add a New Column for the Actual Start Time of the Task

    • Open Power BI Desktop and load your dataset,In the Power Query Editor go to the Add Column tab
    • In dialog box, givea name to the new column . For example, enter ActualStartTime as the column name.
    • In the formula area,enter the below code

    = [ShiftStartTime] + #duration(0, 0, [ActualStartTimeOffset], 0)

     

    Add a New Column for the Actual Duration of the Task

    • Again, go to the Add Column tab. Click on Custom Column, name new column as ActualDuration.
    • Use the following formula to calculate the duration in hours

    = Duration.TotalHours([ActualEndTime] - [ActualStartTime])

     

    Convert the Duration to an Integer Value

    •  Go to the Add Column tab. Click on Custom Column.
    • Name the new column as Duration In Integer.
    • Use the below query

    = Number.RoundDown([ActualDuration])

    Hope the steps provided will be helpful in guiding you to create custom columns.If so,

    please consider marking Accept as solution to assist other members in finding it more easily.

    If you continue to face issues, feel free to reach out to us for further assistance!