Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
Hi!
I think, that may help:
InvEntryTime = FORMAT(AIRActionHistory[ScanImportDate] - AIRActionHistory[FirstActionDate],"##.##")
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |