How to calculate hours using DATEDIFF()? SOLVED

[croberts21]

I have Free PBI Desktop v2.105 (64 bit free version May 2022) on 64-bit Windows Server. I have 4 fields in a table called NonChargeTime: startdate, starttime, enddate, endtime. In power query the startdate and enddate are both of type Date. Starttime and enddtime are both of type Time. 

When I try to make a measure using DATEDIFF PBI won't let me enter any date field. How do I calculate the difference in hours between these 2 times? It's possible the dates are different. I thought it would work like this: 

Measure = DATEDIFF(startdate & " " &  TIME(starttime), enddate & " " TIME(enddtime),HOUR)

And this wouldn't let me enter the date field either: Measure = DATEDIFF(DATEVALUE(startdate)...

Ex:
Startdate: 7/1/2022

Starttime: 11:00:00 PM

Enddate: 7/2/2022

Endtime: 1:00:00 AM

The difference is 2 hours.

More example data:

Startdate: 7/14/2022

Starttime: 8:00:00 AM

Enddate: 7/14/2022

Endtime: 5:00:00 PM

Duration is 9 hours in this case.

The data comes from an ODBC connection to a Postgresql database, so I assume PBI converts the date fields to a DATE datatype, and I'm not sure what the time fields (a string in Postgresql) would be converted to in PBI. This worked fine in tutorials I watched but it doesn't work for me.

Is DATEDIFF even the right function for me to use? 

Can anyone help out please? Thank you.

-----

I have solved this. This is how I did it. 

1. We need to merge the StartDate and StartTime into a new column, and do the same for the EndDate and EndTime columns.
2. In Power Query (Transform mode) select StartDate and Starttime columns.
3. Click Add Column tab. Click merge, choose space as a delimiter. Make a new name called StartDateTime.
4. Change the data type of this new column to DateTime.
5. Do the same for the EndDate and EndTime, make a merged column called EndDateTime. Change the type to DateTime.
6. Click Close and Apply.
7. Go back to Report mode. Make a new column with this formula: Hours = DATEDIFF(StartDateTime, EndDateTime, MINUTE)/60
8. Also in Report mode, for this Hours column, click Column Tools tab. Change the type to Decimal Number, change decimal places to 2.
9. Done.

[JorgePinho · Accepted solution]

DATEDIFF is the right function to use.

My suggestion is to user PowerQuery to merge the Date and Hour together in one column (StartDateHour and EndDateHour).

Then you can do something like DATEDIFF(StartDateHour, EndDateHour, HOUR)