Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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