I have come across a snap when trying to calculate the difference between two dates.
As in my example, I tried to calculate the difference between two dates using the "datediff" function for both monthly and days but the result seem way off and I dont know why.
I have tried the following to get the number of days:
Date difference is a calculated column in my table.
Go to Solution.
Date 1 is format type Date/Time and the Date 2 is format type Date, so they should be the same, but I have changed both the date(s) to Date format and still get the same issue.
View solution in original post
To Calculating the difference between two dates in a decimal number of months I use:
Date.Month([EndDate])-Date.Month([StarDate]) + ((Date.Day([EndDate])/Date.DaysInMonth([EndDate])) +(Duration.TotalDays(Date.EndOfMonth([StarDate])-[StarDate])/Date.DaysInMonth([StarDate]))-1))
Did you make sure their formats are the same (dd/MM/yyyy)?
There could be a format mismatch.
@AnonymousThanks for your fast response, I did originally try it as a measure, but it didnt work. And after researching it on google, it said that you should put it in as a calculated column not measure.
Click here to read more about the December 2021 Updates!
Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.
Mark your calendars and join us for our next Power BI Dev Camp!