How to get the difference between 2 date/time using measure

[Anonymous]

Hi Guys,

Newbie here,

Is there a way to create a measure to calculate the difference between 2 date/time.

I can't create a calculated column because my datasource is a streaming dataset.

Thank you for answering!

[Greg_Deckler]

Measure = DATEDIFF(MAX([DateTime]),MIN([DateTime]),MINUTE) ? 

[Anonymous]

Hi @Greg_Deckler,

It has an error whenever I put it in the table or any visual.

[Greg_Deckler]

Hopefully you didn't just copy and paste that code, it was an example that would need to be modified to fit your situation. But, since you didn't provide much information I just made up column names and such.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi @Greg_Deckler ,

I've tried to modify it using the column names of my table and that's the error that occurs.

I wanted to calculate  the difference between the datetimestamp it was recorded and the current datetime for each row.

Below is my sample dataset

Since it's a streaming dataset, I can't modify the columns and add a calculated column to compute for the difference of each row.

So is there a way that i can get the difference between each date/time stamps and the current date/time for each row without using calculated column?

Thank you @Greg_Deckler 🙂 Hope this helps for the clarification of my question.

[az38 · Accepted solution]

Hi @Anonymous 

as I can suggest, the column named [Duration] can not store Date Type, it stores Period that is not equal Dates

So, in your case you cane re-write @Greg_Deckler statement like

Measure = DATEDIFF(MAX([DateTime]), NOW(), MINUTE)

[Anonymous]

Hi @az38 ,

I am now able to view the result whenever I added it to the table, however, I think the result is not correct. Current time right now is 4/18/2020 10:26:00 PM and the duration results are way too far when I use the measure.

Here is the screenshot:

Sorry to be so newbie right here. Hope you can understand.

Thank you so much!

[az38]

@Anonymous 

create and put into visual new measure

=NOW()

to debug this issue

[Anonymous]

Hi @az38 ,

I've checked what you've said and notice that the NOW() function gives me an incorrect time based on my current time on my PC right now. How do i modify this ? I see some fix but it requires the use of power query however i can't use it right now because of the dataset that i am currently using. Is there any other way to fix this ? 

Thank you so much @az38 !

[az38]

@Anonymous
Do you really need to fix it if your users work with the server data and Date/time and see it in appropriate form?
The only way I see in your case - to change your machine timezone