cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
UK_User123456
Resolver I
Resolver I

Calculating difference between two dates

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 PYLast Gift DateDate Difference Correct result
31/03/201917/06/201968 3
04/03/201903/06/2019113 3
01/03/201903/06/201915 3
02/07/201822/05/2019113 11
     
Days    
Last Gift PYLast Gift DateDate Difference Correct result
31/03/201917/06/20192071 78
04/03/201903/06/20193420 91
01/03/201903/06/20193458 94
02/07/201822/05/20193431 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

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

View solution in original post

4 REPLIES 4
afdelgado
Regular Visitor

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
Not applicable

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

There could be a format mismatch.

 

BR,

Abel

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

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.