cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors