Forum Discussion
calculate time values in one column
Hello everyone,
I'm new to Power BI and could use your help.
- I have an Excel sheet which contains tasks (repeating) and time stamps for end and start of task
- I want to calculate, categorize and visualize the needed times for my tasks + the waiting time inbetween two tasks
- Excel sheet looks like this
| Task | Time Stamp |
| Task n Start | dd.mm.yyyy hh:mm:ss |
| Task n End | dd.mm.yyyy hh:mm:ss |
| Task n+1 Start | dd.mm.yyyy hh:mm:ss |
| Task n+1 End | dd.mm.yyyy hh:mm:ss |
| ... | ... |
My goal is a table that looks like that:
Task 1 time needed = Task n End - Task n Start
Task 1-2 waiting time = Task n+1 Start - Task n End
| Task 1 time needed | Task 1 - 2 waiting time | Task 2 time needed | Task 2-3 waiting time | ... |
| hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | ... |
| hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | ... |
| hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | ... |
| ... | ... | ... | ... |
Step 1:
- imported this sheet in PBI
- problem: cant calculate in one column
- solution: created calculated columns containing the time stamps of each task by using a simple if formula (if [Task] = Task n; [Time Stamp]; blank() )
Step 2:
- converted each calculated column into list, then back into table
- deleted empty rows
- tried to merge them back together, values are in different rows -> no calculation
Step 3:
- back to before merging my tables together
- added index columns to each table
- am now able to calculate time needed for each task using calculated columns with DAX related([Index]) Formula
- table looks like this
| Index | Time Stamp for Task n Start | needed time for Task n |
| 1 | dd.mm.yyyy hh:mm:ss | hh:mm:ss |
| 2 | dd.mm.yyyy hh:mm:ss | hh:mm:ss |
Step 4:
- created new table with DAX selectcolumns([needed time for task n])
- tried to create more calculated columns in this table for the other tasks -> ERROR
!HELP!
I missed out on waiting time for the next task bc its basically the same..
Questions:
1) is there a better solution to my mission? This one seems kind of complicated and isnt working in the end
2) in the end i want a table like shown above which im able to visualize only from the data from my excel sheet
thank you in advance!
Philipp
Hi Anonymous ,
The "greenish functions" are variables, you can get more details from this blog. You can check the applied steps of source query in Power Query Editor.
Please give some sample data in your model, later we will provide you the solution suitable for your scenario.
Best Regards
Rena
11 Replies
Hello everyone,
I'm new to Power BI and could use your help.
- I have an Excel sheet which contains tasks (repeating) and time stamps for end and start of task
- I want to calculate, categorize and visualize the needed times for my tasks + the waiting time inbetween two tasks
- Excel sheet looks like this
Task Time Stamp Task n Start dd.mm.yyyy hh:mm:ss Task n End dd.mm.yyyy hh:mm:ss Task n+1 Start dd.mm.yyyy hh:mm:ss Task n+1 End dd.mm.yyyy hh:mm:ss ... ...
time needed for task n = task n Start - Task n End
waiting time for task n+1 = task n+1 Start - Task n End
etc.
Step 1:
- imported this sheet in PBI
- problem: cant calculate in one column
- solution: created calculated columns containing the time stamps of each task by using a simple if formula (if [Task] = Task n; [Time Stamp]; blank() )
- now my PBI table looks like this
Task Time Stamp Task n Start Task n End Task n+1 Start Task n+1 End ... Task n Start Task n Start dd.mm.yyyy hh:mm:ss dd.mm.yyyy hh:mm:ss Task n End dd.mm.yyyy hh:mm:ss dd.mm.yyyy hh:mm:ss Task n+1 Start dd.mm.yyyy hh:mm:ss dd.mm.yyyy hh:mm:ss Task n+1 End dd.mm.yyyy hh:mm:ss dd.mm.yyyy hh:mm:ss ... ...
...
...
Task n Start dd.mm.yyyy hh:mm:ss
dd.mm.yyyy hh:mm:ss
Task n End dd.mm.yyyy hh:mm:ss
Step 2:
- converted each calculated column into list, then back into table
- deleted empty rows
- tried to merge them back together
- problem: table looks like this and i still cant calculate bc values arent in one row
Task n Start Task n End ... dd.mm.yyyy hh:mm:ss dd.mm.yyyy hh:mm:ss ... dd.mm.yyyy hh:mm:ss dd.mm.yyyy hh:mm:ss
Step 3:
- back to before merging my tables together
- added index columns to each table
- am now able to calculate time needed for each task using calculated columns with DAX related([Index]) Formula
- table looks like this
Index Time Stamp for Task n Start needed time for Task n 1 dd.mm.yyyy hh:mm:ss hh:mm:ss 2 dd.mm.yyyy hh:mm:ss hh:mm:ss Step 4:
- created new table with DAX selectcolumns([needed time for task n])
- tried to create more calculated columns in this table for the other tasks -> ERROR
!HELP!
I missed out on waiting time for the next task bc its basically the same..
Questions:
1) is there a better solution to my mission? This one seems kind of complicated and isnt working in the end2) in the end i want a table im able to visualize only from the data from my excel sheet and looks like this:
Task 1 time needed Task 1 - 2 waiting time Task 2 time needed Task 2-3 waiting time ... hh:mm:ss hh:mm:ss hh:mm:ss hh:mm:ss ... hh:mm:ss hh:mm:ss hh:mm:ss hh:mm:ss ... hh:mm:ss hh:mm:ss hh:mm:ss hh:mm:ss ... ... ... ... ... thank you in advance!
v-lili6-msftCommunity Support
hi Anonymous
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Regards,
Lin
Hi v-lili6-msft ,
accidentally i made 2 Posts, there's already a discussion going on, see here:
https://community.powerbi.com/t5/Desktop/calculate-time-values-in-one-column/m-p/1392732
i added sample data and my expected result.
Thank you!
Kind RegardsPhilipp
Hi Anonymous ,
I created a sample pbix file base on your requirement, please check if that is what you want. If no, please provide some sample data and your desired result. And if it involve any other calculation, please provide the related calculation logic. Thank you.
Best Regards
Rena
Hi Anonymous Rena,
Thanks for your reply and your help.
The final table 2) you created looks exactly like i wanted it.
Problem is your source table, you created 2 columns with End and Start of each task, my source table only contains one column "Time Stamp" with End and Start in one column.
Sorry it's in German "Nomenklatur" are my tasks, i created a model with just 5 tasks, repeating 2 times.
My first step was to separate Start and End of my tasks, i created tables for each task.
I can now calculate between them but cant find a way to create the final table containing all tasks with all calulacted times.
I can now calculate between them but cant find a way to create the final table containing all tasks with all calulacted times.
"Intervall" is my time needed for a task for simplifying reasons i missed out on waiting times in the first model.
Thank you in advance!
Kind Regards
PhilippHi Anonymous ,
The "greenish functions" are variables, you can get more details from this blog. You can check the applied steps of source query in Power Query Editor.
Please give some sample data in your model, later we will provide you the solution suitable for your scenario.
Best Regards
Rena