Hello,
I have a question regarding the dateDiff function. I am working with data from the last 16 months and aim to calculate the Daily Average PL using the following basic formula:
sum(pl) / dateDiff({startdate}, {enddate}, ‘DD’)
However, I am encountering an issue where the dateDiff function sometimes returns 29 days instead of 30, or 30 days instead of 31 for certain months. This inconsistency is affecting my calculations.
If you see column date difference Over there you can see the function is returning with number of rows between the dates
Formula used: dateDiff(enddate, startdate ,‘MM’)
For further reference, I have created the table in my analysis from my database which I am using to calculate the Daily Average PL, shown in the screenshot.
Hello @Dhiru, welcome to the QuickSight community!
I am wondering if the inconsistent return values from the dateDiff calculation have something to do with the formatting of the date field. If you use truncDate to convert both date values to a Day, maybe the results would be more consistent:
If you still have issues with the returned values after this change, could you create a demo version of your analysis in QuickSight Arena so I can test the calculations in an analysis? That will make it easier for me to help you resolve the issue. Thank you!
