Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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
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
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
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
@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
That's great.
Happy to help
Regards,
Chayan Upadhyay
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
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
75 | |
49 |
User | Count |
---|---|
142 | |
141 | |
110 | |
69 | |
55 |