DateDiff adding an extra day?

[ac10304]

I have a simple DateDiff function to calculate the # of months between a "date received" column and whatever the current date is.

My function is: Num Months Open = DATEDIFF(Table1[Date Received], TODAY(), MONTH)

I filtered to show only 7-9 months.

On the left, the last row shows a date received value of 2/21/2018, which shouldnt be 7 months it should be 6 months because today is 9/20/2018. In excel, it shows the right number of returned values, but in Power BI it shows 2/21/2018 as being 7 months.

I'm not sure why it's doing this, essentially DateDiff in power bi should be the same as DateDif in excel right?

I'd appreciate any help on this, i'm stuck 😞

[v-jiascu-msft]

Hi @ac10304,

Could you please mark the proper answers as solutions?

Best Regards,
Dale

[Anonymous · Accepted solution]

Hi @ac10304

DATEDIFF in Power BI doesnt work that way. When you specify the interval as Month, it takes the Month of Date1 & Date 2 and finds the difference. So in your case, 9-2= 7. Even if you find the differene between 2/28/1028 and today in terms of month, it will be 7.

Hope this clears your doubt.

Thanks
Raj

[ac10304]

THanks for your reply @Anonymous, this helps clear up my confusion.

A follow up question: is there a way to get the results like in excel?
Or is it good practice to have it round up as it shows in power bi

thanks again

[Anonymous]

Hi @ac10304

The below URL has the details of the similar issue.

https://community.powerbi.com/t5/Desktop/DATEDIFF-month-interval-not-working-as-expected/td-p/236011

Can you try this ?

Month Diff =
( 1   * ( [Date2] - [Date1] ) )
    / DAY ( EOMONTH ( [Date2], 0 ) )

Thanks
Raj