cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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

New forum boards available in Real-Time Intelligence.

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

Power BI Monthly Update - May 2024

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

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors