Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
turbea
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

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
Azunai
Frequent Visitor

Hi!

 

I think, that may help:

 

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

Back2Basics
Helper IV
Helper IV

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

Unfortunately I got 6.78 as result instead of 6.44.

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

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

 

Many thanks!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.