Calculating difference between two dates

[UK_User123456]

Hi All,

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.

Monthly
Last Gift PY	Last Gift Date	Date Difference		Correct result
31/03/2019	17/06/2019	68		3
04/03/2019	03/06/2019	113		3
01/03/2019	03/06/2019	15		3
02/07/2018	22/05/2019	113		11
Days
Last Gift PY	Last Gift Date	Date Difference		Correct result
31/03/2019	17/06/2019	2071		78
04/03/2019	03/06/2019	3420		91
01/03/2019	03/06/2019	3458		94
02/07/2018	22/05/2019	3431		324

I have tried the following to get the number of days:

Date Difference Last Gift (By Day) = DATEDIFF([Last Gift PY],'Constituent Last Gift Data'[Last Gift Date],DAY )

and the following for month:

Date Difference Last Gift (By Day) = DATEDIFF([Last Gift PY],'Constituent Last Gift Data'[Last Gift Date],Month )

Date difference is a calculated column in my table.

TIA

[Anonymous]

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

[Anonymous]

Did you make sure their formats are the same (dd/MM/yyyy)?

There could be a format mismatch.

BR,

Abel

[UK_User123456 · Accepted 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.

[UK_User123456]

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