Forum Discussion
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 take 4 hours and on the day it takes 5 hours, it should be documented.
I have imported everything correctly but would need some help.
So there is not an actual duration column for every task that has been completed. I need to calculate it myself.
I have the columns: ShiftStartTime & ActualStartTimeOffset (This gives the amount of minutes that task started after shift started)
How can I add ShiftStartTime (date/time format) to ActualStartTimeOffSet (minutes, integer value for example, 300 (which Is 5 hours))?
I would then need to subtract ActualEndTime - ActualStartTime which would get the amount of hours it took for the task to be done in only integer value.
Can someone help with how I can do this in powerqueryeditor and by adding new column for the actual start time of a task and then actual time It took?
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!
6 Replies
For the first question, add new custom column and use the following formula
DateTime.AddMinutes([ShiftStartTime], [ActualStartTimeOffset])
For the second question add another custome column and use the following formula
Duration.TotalMinutes([ActualEndTime] - [ActualStartTime])
- How can I do this as a calculated column? So if offset minutes are stored in a diff table than the shift start time, what will my M code will be?
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.
Can you show how you made this using all formulaes etc M query and steps please cheers?
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!
