Time based progress bar using current datetime

[GuntherCoppens]

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

[mwegener · Accepted solution]

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. 

[v-jayw-msft · Accepted solution]

Hi @GuntherCoppens ,

Please check the measure below.

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

 

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]

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?

[v-jayw-msft]

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]

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?

[mwegener]

Hi @GuntherCoppens ,

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

[mwegener · Accepted solution]

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.