cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GuntherCoppens
Regular Visitor

Time based progress bar using current datetime

Hi,

 

I am looking for a way to show progress as in a progress bar. I have a table where records are inserted and the most important fields on that table are:

  • datetime created
  • duration

So you could say that the start time of the process is datetime created, while the expected end time of the process is datetime created + duration. Now I need to show progress versus "now" if that makes sense.

 

As an example:

  • datetimecreated = Feb 10th 2020, 22:00:00pm
  • duration = 600s (also known as 10 minutes)

 

With the preview feature of page refresh, I can refresh like every second on desktop (not saying this is good).

 

But what I want to achieve that if you refresh the above example on Feb 10th ar 22:05:00 it shows 50%, while if you refresh on Feb 10th at 22:09 it shows 90%, ...

 

Is that feasible?

 

Appreciate you reading this and thinking about it 🙂

 

Thanks

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: Time based progress bar using current datetime

Hi @GuntherCoppens ,

 

you can try this.

 

Progress = DIVIDE(DATEDIFF('Table'[datetimecreated],NOW(),SECOND),'Table'[duration])

 

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support. 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Proud to be a Super User!

View solution in original post

Microsoft v-jayw-msft
Microsoft

Re: Time based progress bar using current datetime

Hi @GuntherCoppens ,

 

Please check the measure below.

Measure = FORMAT(DATEDIFF(SELECTEDVALUE('Table'[datetimecreated]),NOW(),SECOND)/SELECTEDVALUE('Table'[duration]),"percent")

 4.PNG

5.PNG

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Super User III
Super User III

Re: Time based progress bar using current datetime

Hi @GuntherCoppens ,

 

you can try this.

 

Progress = DIVIDE(DATEDIFF('Table'[datetimecreated],NOW(),SECOND),'Table'[duration])

 

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support. 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Proud to be a Super User!

View solution in original post

Microsoft v-jayw-msft
Microsoft

Re: Time based progress bar using current datetime

Hi @GuntherCoppens ,

 

Please check the measure below.

Measure = FORMAT(DATEDIFF(SELECTEDVALUE('Table'[datetimecreated]),NOW(),SECOND)/SELECTEDVALUE('Table'[duration]),"percent")

 4.PNG

5.PNG

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

GuntherCoppens
Regular Visitor

Re: Time based progress bar using current datetime

Impressive, this is surely pointing me in the right direction.

 

Now what I do experience is that I key in for example 21pm in the GUI of the app in which I am creating the records, however PowerBI is showing this as 20pm it seems. I am in CET or UTC+1. Power BI regional settings is also set to Dutch (Belgium), so that looks to be ok. 

 

Where could that 1 hour difference be coming from?

Super User III
Super User III

Re: Time based progress bar using current datetime

Hi @GuntherCoppens ,

 

I would first check the value in the database with a simple SQL client

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Proud to be a Super User!

Microsoft v-jayw-msft
Microsoft

Re: Time based progress bar using current datetime

Hi @GuntherCoppens ,

 

The first thing i can thought is your data source has different time zones with Power BI.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

GuntherCoppens
Regular Visitor

Re: Time based progress bar using current datetime

Hi - It has.

 

SQL stores in UTC. Normally a client adapts itself according to the regional settings of the operating system of the client, correct?

 

So in the Business Central Client I key in 23pm (my regional settings are UTC+1), this gets stored in SQL as 22pm (UTC).

Now PowerBi is also set to UTC+1, and it shows 22pm.

 

Thoughts?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors