Calculate hours between two date/time column

[sathyaramesh]

Hi

I have a two calculated column
Col1 Col2
27-12-2019 04:35:00 28-12-2019 05:24:00

Need to calculate hours between col2-col1

Both column have date/time datatype

I need a total hours between the date.

Thanks in advance

[v-gizhi-msft]

Hi,

Please try this calculated column, and this can easily reach your requirement:

Total Hour = DATEDIFF('Table'[Col 1],'Table'[Col 2],SECOND)/3600

The result shows:

If my answer has solved your issue, please mark it as a solution for others to see.

Thanks!

Best Regards,

Giotto Zhi

[Ashish_Mathur]

Hi,

Try this calculated column formula

=Data[Col2]-Data[Col1]

Format this column as Date/Time.

Hope this helps.

[sathyaramesh]

Hi
I need total hours between the two column

Thanks

[Ashish_Mathur]

Hi,

This calculated column formula works

=DATEDIFF([Col1],[Col2],MINUTE)/60

Hope this helps.

[edhans]

Do this in Power Query. Your model will perform better, and it is a simple formula.

Add a new Custom Column in Power Query and add this formula, then round as desired.

= Duration.TotalHours([Col1] - [Col2])

 

[sathyaramesh]

Hi @edhans 

Thanks for the replay but I have done some calculation for getting (calculated column) date/time for the table. so i need dax to achieve this in front side. 

To create column in query editor is difficult for me.

is there any way to achieve in dax?

[edhans]

@Ashish_Mathur did it in DAX, which will work better for you since your dates were created in DAX. Consider using Power query in the future for all columns though.  It is just good practice. The problem is calculated columns are very "excel-like" and Excel users jump on them, but that is not a good long term practice, especially for large models.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

[sathyaramesh]

@Ashish_Mathur @edhans @Greg_Deckler 

i used 

datediff =datediff(startate,enddate, hours)

@Ashish_Mathur  i tried this too

=DATEDIFF([Col1],[Col2],MINUTE)/60

i am getting 0

is there any problem in the format?

[edhans]

@sathyaramesh are your columns really dates and times? They must be for this to work. They cannot be text that looks like it. That might be why you are getting zero. Check out this CONVERT() function in DAX and see if that helps. 

[sathyaramesh]

Hi @edhans 

The column is of date/time column.

[Greg_Deckler]

datediff =datediff(startate,enddate, HOUR)

Not hours, make sure that is correct.

[Greg_Deckler]

Yep, works

[sathyaramesh]

Hi @Greg_Deckler @edhans @Ashish_Mathur 

Sorry for my mistake. I made some mistake in calculated column.(I used both date as (start date time) for finding difference).

i think its working fine. Thank you so much for all your replay

[Greg_Deckler]

No, my apologies @sathyaramesh, that one is all on me, I actually originally posted ,HOURS and moved on and it bugged me whether the syntax was actually HOUR or HOURS but I didn't check, I figured DAX intellitext would save you if I got it wrong. My bad.

[edhans]

Please mark the one that finally nailed it @sathyaramesh as the solution so the thread will be solved and others can see the answer easily

[Ashish_Mathur]

Hi,

Mine is a calculated column formula - not a measure.

[sathyaramesh]

@Ashish_Mathur 

Datediff = DATEDIFF(Table[start date time final],Table[End date time final],Hours)

[sathyaramesh]

Hi @Ashish_Mathur 

i have also created calculated column

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Greg_Deckler]

Column = DATEDIFF([Col1],[Col2],HOURS)