Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 😞
Solved! Go to 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
Hi @ac10304,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
76 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |