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

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.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

v-jayw-msft
Community Support
Community Support

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.

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

View solution in original post

6 REPLIES 6
v-jayw-msft
Community Support
Community Support

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.

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

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?

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.

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

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?

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.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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.