Datediff function with decimals

[Anonymous]

Hi everyone,

I am using the function Datediff( ), but the results I am getting are integers, and I need than these ones become decimals.

Example:

Reuslt: 1215

Expected: 1214.7

Any suggestions?

Thanks

[ninhtran1301]

The way around it that worked for me is to make the unit seconds in datediff function then divide by 3600 if you want it in hour then it will be more accurate. Depends on the datetime format you're trying to subtract.

For example in my case I want to get the hour difference in Start and stop time below:

calculate(DATEDIFF(min('Scrub Tool'[START_TIME]),max('Scrub Tool'[STOP_TIME]),SECOND),filteredrows)/3600

Btw, this is in DAX. 

[i-technology]

How about this DAX formula

var nbrMonths = YEARFRAC(startDate, endDate) * 12
var result    = DIVIDE(something, nbrMonths, 0)

[c_cook28954]

I was having an issue where I was using the DateDiff function in DAX and it was producing a rounded down number of months 

Ie, if a transacted occurred on the 1st of Jan, 2023 and today is the 7th of July, 2023 the result returned for a DATEDIFF between the values was 6 months even though, technically, its 6 and a bit months. 

For my logic, 6 and a bit months needed to be rounded up to 7 months

I tried rounding functions on the DATEDIFF function but the realized it wouldn't work as a DATEDIFF doesn't produce a decimal

Using your function produced the decimal and allowed the round function to work 

This is my end expression for reference:

Months Since Last Transacted = 
VAR LastTransacted = 
IF(
    ISBLANK(PTStock_Data[LAST_DEM_DATE]) && 
	ISBLANK(PTStock_Data[Last_Invoiced_Datetime]), 
    PTStock_Data[CREATION_DATE],
    IF(
        ISBLANK(PTStock_Data[LAST_DEM_DATE]) && 
		NOT(ISBLANK(PTStock_Data[Last_Invoiced_Datetime])),
        PTStock_Data[Last_Invoiced_Datetime], 
        PTStock_Data[LAST_DEM_DATE]
    )    
)
VAR MonthsSinceTransacted = 
ROUNDUP( YEARFRAC( LastTransacted, TODAY() ) *12, 0 )

RETURN
MonthsSinceTransacted

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Change date type and format as below:

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Stevoisiak]

Does this account for Daylight Savings Time?

[amitchandak]

What are you taking as date diff? Assume you are taking Day , Then take in hours and divide by 24

Diff = datediff(date1,date2,day)

Diff = datediff(date1,date2,Hour)/24

Same is true with other types

[Greg_Deckler]

Can you just use subtraction?

( Date1 - Date/Time ) * 1.