cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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.

Regards

1 ACCEPTED SOLUTION
Employee

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

6 REPLIES 6
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
Employee

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

Resolver III

@Anonymous

you Can Try this

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

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

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors