cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DATEDIF months calculation

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

1 ACCEPTED SOLUTION
v-huizhn-msft
Microsoft
Microsoft

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

View solution in original post

6 REPLIES 6
rodrigosoaresss
Regular Visitor

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
Microsoft
Microsoft

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

View solution in original post

Anonymous
Not applicable

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

 

Thank you!

chethan
Resolver III
Resolver III

@Anonymous

 

you Can Try this 

 

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

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
Not applicable

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.