DATEDIFF(TODAY();Table[Date]) not working...

[Asantos2020]

I have a table showing:

Date          PO Number   Customer

07/06/19      88888          InterCompany

"dd/mm/yyyy"

I need to come up with a column showing how many days the PO has been sitting.

I do use DATEDIFF(TODAY();Table[Date];DAY), but it's giving me 72 days, while it should be 24 days.

I've tried formating today to reflect the Date Column's format, but it's giving the same result.

Any solution at sight for this one!?

Thanks a bunch in advance.

Regards,

Antonio Santos

[chayanupadhyay · Accepted solution]

@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

[chayanupadhyay]

This 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

[Asantos2020]

Hello @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 · Accepted solution]

@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 · Accepted solution]

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

[chayanupadhyay]

That's great.

Happy to help 

Regards,

Chayan Upadhyay

[dax]

Hi Asantos2020,

I can’t reproduce your problem, I assume that this might be related to date format, you could try to change date format in Query Editor like below , then use expression to see whether it works or not

Best Regards,
Zoe Zhi

[parry2k]

@Asantos2020 doesn't make sense, can you drop Table[Date] and newly added date difference column in a table visual and share the screen shot.