Forum Discussion
Advocate IIDATEDIFF(TODAY();Table[Date]) not working...
Asantos2020 I have tried with the sample data present in snapshot and i am getting the correct difference days.I have the DataPedido column which has dates in "MM/DD/YYYY" format which i changed in the "DD-MMM-YY" and then created a calculated column to find out the days using the DATEDIFF() function and even if i don't change the format of DataPedido and still be in DD/MM/YYYY i am getting the correct difference days.
I know this won't help much for you to solve the issue.. but you can try to change the date format of DataPedido column to DD/MM/YYYY and check if you are getting the correct result.
Please do let us know if you are able to solve the issue.
Thank you,
Chayan Upadhyay
Hi chayanupadhyay ,
My problem persisted, even after I've changed the date format to match each other's. But I have just found out what was causing it:
The field was aggregating, considering the fact that there is more than one row containing that PO number and dates. So it was summing the days!
Thank you for your help!
Regards,
Antonio
Helper IIIThis should not happen, you need to check the date format as per your locale, i used the same function with the date range you mentioned and it is showing me 24
i have tried with hardcoded dates in DD/MM/YYYY format and it is showing me the different number it should show 24 but showing 180 since it is considering the first part in date as month and it might be due to Region i belong to where DATEDIFF is expecting in MM/DD/YYYY format (not sure on this) or it may be the default format for this function.
I tried one more thing by changing the regional setting from English(US) to English (Australia) and save the report and re open just to check if the DATEDIFF function changes the output but still it is showing 180.
Hope this will help !!!
Please add your inputs if my understanding is incorrect.
Regards,
Chayan Upadhyay
Asantos2020Hello chayanupadhyay , dax , parry2k !
Thanks for the taking the time to help.
Below, you can see that the DAX expression is giving me by row:
The first row is correct, but the second, being 75, makes no sense. I've tried formatting like FORMAT(UTCTODAY();"dd/MM/yyyy");DAY), but it gives the same result as the one above.
I have also changed Locale setting on Power Query for those columns to Date and Portuguese (Brasil).
Appreciate your attention!
Antonio
- chayanupadhyay
Asantos2020 I have tried with the sample data present in snapshot and i am getting the correct difference days.I have the DataPedido column which has dates in "MM/DD/YYYY" format which i changed in the "DD-MMM-YY" and then created a calculated column to find out the days using the DATEDIFF() function and even if i don't change the format of DataPedido and still be in DD/MM/YYYY i am getting the correct difference days.
I know this won't help much for you to solve the issue.. but you can try to change the date format of DataPedido column to DD/MM/YYYY and check if you are getting the correct result.
Please do let us know if you are able to solve the issue.
Thank you,
Chayan Upadhyay
- Asantos2020
Hi chayanupadhyay ,
My problem persisted, even after I've changed the date format to match each other's. But I have just found out what was causing it:
The field was aggregating, considering the fact that there is more than one row containing that PO number and dates. So it was summing the days!
Thank you for your help!
Regards,
Antonio