Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Asantos2020
Advocate II
Advocate II

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

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

2 ACCEPTED SOLUTIONS

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

 

Capture.JPGCapture12.JPG

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

View solution in original post

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

View solution in original post

7 REPLIES 7
chayanupadhyay
Helper III
Helper III

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

Capture.JPG

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.

Capture1.JPG

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

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:

PO's awaiting.JPGDays awaiting.JPG

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

 

 

 

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

 

Capture.JPGCapture12.JPG

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

That's great.

Happy to help Smiley Happy

 

Regards,

Chayan Upadhyay

dax
Community Support
Community Support

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

1.png2.png

Best Regards,
Zoe Zhi

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.