Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kgathright
Helper II
Helper II

New Column guidance

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! 

9 REPLIES 9
v-chuncz-msft
Community Support
Community Support

@Kgathright 

 

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...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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? 

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

 

And it's update: https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639?search-a...

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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...


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.