Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Datediff function with decimals

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

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

8 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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's avatar
      Stevoisiak
      New Member
      1 year ago

      Does this account for Daylight Savings Time?

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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

  • i-technology's avatar
    i-technology
    New Member
    4 years ago

    How about this DAX formula

    var nbrMonths = YEARFRAC(startDate, endDate) * 12
var result    = DIVIDE(something, nbrMonths, 0)
    • c_cook28954's avatar
      c_cook28954
      Advocate II
      3 years ago

      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's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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
  • ninhtran1301's avatar
    ninhtran1301
    Regular Visitor
    1 year ago

    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.