Forum Discussion
DateDiff adding an extra day?
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 :(
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
4 Replies
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
RajTHanks 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 bithanks again
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
