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 suggesti...
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.

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