DATEDIF months calculation

[Anonymous]

Hey guys, I'm having troubles with the DATEDIF function.

I'm trying to calculate the month difference between a project end date and the date of today.

I'm using:

Month Delay = DATEDIFF(ProjectCenter[Finish],TODAY(),MONTH)

And, for example, I get as result 3 in a project which ends 5/31/2018.

There are almost 25 days to that date, so the month delay is 2 and decimals.

If I use the Datedif function in EXCEL, I get the correct result.

Thank you for your help.

Regards

[rodrigosoaresss]

That's how I've done it

HELP =

VAR I = InicialDate

VAR F = FinalDate

VAR __t =

DIVIDE (

EOMONTH ( I, 0 )

- ( I - 1 ),

EOMONTH ( I, 0 )

- EOMONTH ( I, -1 )

)

+ DATEDIFF (

EOMONTH ( I, 0 ) + 1,

EOMONTH ( F (), -1 + 1 ),

MONTH

)

+ DIVIDE (

F ()

- ( EOMONTH ( F (), -1 ) + 1 ),

EOMONTH ( F (), 0 ) - EOMONTH ( F (), -1 )

)

return __t

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

Please use the following formula and check if it works fine.

MonthDelay=ROUND(DIVIDE(DATEDIFF(DATE(2018,5,31),TODAY(),DAY),30),0)

Best Regards,
Angelia

[Anonymous]

I will try this function over the course of the days. But I must say that the result is the expected.

Thank you!

[chethan]

@Anonymous

you Can Try this 

Month Delay =
VAR Dateduration = 'Sales Data'[Ship Date] - 'Sales Data'[OrderDate]
RETURN
    DIVIDE ( Dateduration, 12, 0 )

[Anonymous]

Hi @Anonymous

When you mention the interval as Month, Power BI extract the month number and calculate the difference. So, in your example it will be 8-5= 3 and it actually wont calculate the no of days.

Thanks

Raj

[Anonymous]

The only way to solve this is to calculate the day's dif and divide it to 30? 

I think this is not a good way to calculate it since there are months with 31 days and others with 29...