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.
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 is a calculated column in my table.
TIA
Solved! 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.
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.
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.
@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.
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 |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |