cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MH3
Helper IV
Helper IV

Time Difference

Hello Everyone.

 

I have two columns Start Time  and End Time and want to calculate difference between two date/time columns but I can only get the column name by using an Aggregation function like Count, Min, Max.

Why is it like that how can I subtract them and get the desired results?

 

my Data Values here:

Time.png

                         

Ed Time ColumnEd Time Column

                 Start Time ColumnStart Time Column





I want to create a measure,  Need Help!

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@MH3 

You can still create a measure with sum/max/min, and change the to Time format.

 

Measure = SUM('Table (2)'[End])-SUM('Table'[Start])

 

time format.JPG

 

If you cannot make the columns in a same table, you should have a relationship between the two tables.

 

Paul Zheng _ Community Support Team
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
V-pazhen-msft
Community Support
Community Support

@MH3 

You can still create a measure with sum/max/min, and change the to Time format.

 

Measure = SUM('Table (2)'[End])-SUM('Table'[Start])

 

time format.JPG

 

If you cannot make the columns in a same table, you should have a relationship between the two tables.

 

Paul Zheng _ Community Support Team
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

Dear Sir @V-pazhen-msft @amitchandak  @Greg_Deckler 

 

I have used this Formula

Time DF = SUM('Log Header'[EndTime])-SUM('Log Header'[StartTime])

 

and my Table Structure is this:

start time end time.png


when I used the formula in the viusalization it shows me bigger values
Filter is for HOTEL DAVOS, and the Table isSorted  for Hotel Davos as WellFilter is for HOTEL DAVOS, and the Table isSorted for Hotel Davos as Well

, is it correct or worng I can't verify it please help!

@MH3 - If you are referring to the Total, it's a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@MH3 , Try like

Time DF = sumx('Log Header',('Log Header'[EndTime])-('Log Header'[StartTime])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

amitchandak
Super User IV
Super User IV

@MH3 , if they are from two different tables refer

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

 

Will work with same table too



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Greg_Deckler
Super User IV
Super User IV

@MH3  - So, if these are in two different tables as I suspect, then there must be a relationship between the tables that joins them correct? And I would also need to make the assumption that there is something in the visual that uniquely identifies rows in the tables in question like an ID or something. In that case, you can do something like:

 

Measure = DATEDIFF(MAX('Table1'[Start Date]), MAX('Table2'[End Date]),HOUR)

 

That is not your only option (HOUR):

https://docs.microsoft.com/en-us/dax/datediff-function-dax

 

If they are in the same table, then replace 'Table2' with 'Table1', you will still need something in your visual for unique row identification.

 

If these are columns in the same table and you would like a calculated column instead of a measure:

Measure = DATEDIFF('Table1'[Start Date], 'Table1'[End Date],HOUR)

 

Just FYI, you can also do the same calculation as above like this:

'Table1'[Start Date] - 'Table1'[End Date] * 24

 

The integer portion of a date/time column is the number of days since a certain point in time (december 30th, 1899 or something like that). The decimal portion is fractions of a day. You can get minutes 24 * 60 or seconds 24 * 60 * 60. 

 

If this does not work or assumptions are bad 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors