cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Calculating date/time difference

 

Capture.PNG

 

 

 

 

Hello,

 

I would like to calculate the difference between 2 date/time value (one is an additional column, the other is a measure). I need to see the result in days, but in decimal number.

If I just calculate the difference this way, I will get a rounded value as a result:

InvEntryTime = AIRActionHistory[ScanImportDate].[Day] - AIRActionHistory[FirstActionDate].[Day]

As you can see on the attached printscreen, I got 6 days, but I would like to see 6.44 days.

 

How I should calculate the difference to get decimal number as a result?

 

Many thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper III
Helper III

Re: Calculating date/time difference

Try this

 

 

New column = (AIRActionHistory[ScanImportDate]-AIRActionHistory[FirstActionDate])+(((HOUR(AIRActionHistory[ScanImportDate])-hour(AIRActionHistory[FirstActionDate]))+(((MINUTE(AIRActionHistory[ScanImportDate])-MINUTE(AIRActionHistory[FirstActionDate]))/60))/24)

 

I dont think you need the extra .[Date] at the end of things

View solution in original post

6 REPLIES 6
Highlighted
Helper III
Helper III

Re: Calculating date/time difference

I think this would work

 

New column = value(AIRActionHistory[ScanImportDate].[Date])-value(AIRActionHistory[FirstActionDate].[Date])+(((HOUR(AIRActionHistory[ScanImportDate])-hour(AIRActionHistory[FirstActionDate].[Date]))+((MINUTE(AIRActionHistory[ScanImportDate])-MINUTE(AIRActionHistory[FirstActionDate].[Date]))/60))/24)

 

I've not included seconds as I suspect they wil have little impact upon the decimal. If you were interested in including that it would be the same principle, just taking one from the other and dividing by 60 again - but you'll want to include it before the "/24" as that it becomes part of the decimal

Highlighted
Frequent Visitor

Re: Calculating date/time difference

Hi!

 

I think, that may help:

 

InvEntryTime = FORMAT(AIRActionHistory[ScanImportDate] - AIRActionHistory[FirstActionDate],"##.##")

Highlighted
New Member

Re: Calculating date/time difference

Unfortunately I got 6.78 as result instead of 6.44.

Highlighted
New Member

Re: Calculating date/time difference

With the second option I can see the decimals, but they are 00 only. It seems the formula cannot use the time in the date/time format to calculate. Only takes the date.

Highlighted
Helper III
Helper III

Re: Calculating date/time difference

Try this

 

 

New column = (AIRActionHistory[ScanImportDate]-AIRActionHistory[FirstActionDate])+(((HOUR(AIRActionHistory[ScanImportDate])-hour(AIRActionHistory[FirstActionDate]))+(((MINUTE(AIRActionHistory[ScanImportDate])-MINUTE(AIRActionHistory[FirstActionDate]))/60))/24)

 

I dont think you need the extra .[Date] at the end of things

View solution in original post

Highlighted
New Member

Re: Calculating date/time difference

Yes, you are right. I dont need the extra .[Date] thing.

 

Many thanks!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors