New Column guidance

[Anonymous]

I'm working on a dashboard using a gauge and need to accommodate a scenario in which a job doens't kick off.

I have a column 'Expected Run Time' that is elapsed hours, mins, seconds -  example: 02:46:32

I have a column 'Begin Execution Time' that is a time stamp of when the job kicked off -  example: 2020-02-17 04:30:03.390

I need to establish an expected start time based on the previous elapsed hours, mins, seconds and the begin execution time. 

Thanks so much in advance! 

[v-chuncz-msft]

@Anonymous 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Adding-Duration-to-a-Start-time-to-achieve-a-time-value/m-p/143404#M61873

[Anonymous]

Thanks for the suggestions, however since I had complete control over the SQL I solved it there. 

[Anonymous]

I was able to get this DAX to work. I'm still working through getting the differences in the runtimes to be displayed as well. 

Delay = IF([Status] ="Failed", "999", (IF([RunTime]>[AvgRunTime],"120","1")))

[Greg_Deckler]

Theoretically you should just be able to add/subtract your time values, right?

[Anonymous]

I tought that as well, but when I converted the column that is hours, mins, seconds to date time it didn't work. Don't they both have to be date/time stamp to add or subtract from each other? 

[Greg_Deckler]

@Anonymous ah, I missed that one was essentially duration. See if this helps: https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx

And it's update: https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639?search-action-id=101896880406&search-result-uid=793639

You'll probably have to parse out the hours, minutes and seconds from your duration and figure out how many seconds it is and then add that to your datetime value.

[Anonymous]

Would I need to generate tables for hours, minutes and seconds? 

[amitchandak]

Can you share sample data and sample output.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

BeginExecutionTime          EndExecutionTime             RunTime   ExpctdRunTime   AvgRunTime
2020-02-17 04:30:00.127   2020-02-17 07:32:04.497   03:02:04   10:00:00              03:39:11
2020-02-17 04:00:00.237   2020-02-17 04:02:18.260   00:02:18   10:00:00              00:03:41
2020-02-17 04:30:03.390   2020-02-17 06:46:18.747   02:16:13   02:46:32              02:45:05
2020-02-17 04:30:02.970   2020-02-17 04:30:32.343   00:00:28   00:00:30              00:00:30
2020-02-17 06:46:19.217   2020-02-17 07:32:04.450   00:45:45   00:47:41              00:47:35