Forum Discussion
DATEDIFF incorrectly calculating
Hello Power BI Community!
I can't tell you all how much I have loved getting to know how to use Power BI. Thank you for eveyone's contributions on this forum. It's my go to stop for help and most of the time I can find a solution. Unfortunately, I am a bit perplexed at the moment about how the DATEDIFF function works and haven't found this issue on here yet. I have created a calculated column using DATEDIFF to help show how many days between PO Date and Ship Date. It works mostly, but I have found a few instances where the calculation is way off. See below screenshot:
As you can see there are a few instances that are not calculating correctly. The first and third instances show 105 and 74 when they should be showing 21 and 30, respectively . Here is my DAX for that column: Days to Ship = DATEDIFF(ODM[PO Date].[Date],ODM[Shipped Date].[Date],DAY)
Anyone else seen this issue? Or know a fix for this? I know I am still a novice when it comes to DAX, so it may be a simple fix.
Thanks for your help in adanvce!
Hi Anonymous,
Please check if you have more than one row that contains the same PO and Shipped dates. Your Days to Ship might have been aggregated.
PO Date Shipped Date Days to Ship-incorrect Days to Ship-correct Number of Rows? 2/7/2018 2/28/2018 105 21 5 1/17/2018 2/23/2018 74 37 2
13 Replies
Hi Anonymous,
Please check if you have more than one row that contains the same PO and Shipped dates. Your Days to Ship might have been aggregated.
PO Date Shipped Date Days to Ship-incorrect Days to Ship-correct Number of Rows? 2/7/2018 2/28/2018 105 21 5 1/17/2018 2/23/2018 74 37 2 Hi Anonymous,
Have you resolved it? If yes, would you please accept the helpful suggestion as an answer or sharing your resolution so that it can benefit more users?
Regards,
Yuliana Gu
I apologize for the late response. I got pulled onto another project and haven't have the chance to check if there were duplicates that were aggregating until today. Once people see you can do cool things in Power BI, they want you to create a slew of reports and dashboards.
So anyways, what I found was that when I was merging two queries on what I thought was a unique identifier. One of those queries had multiple lines for that "uniuqe identifier" and was indeed aggregrating the number rows and providing the multiplied number of days between the dates. Thank you for everyone's help on this and especially danextian in helping determine the root cause. Again, I apologize for the tardiness in my response. The fix was to just make sure I had a true unique identifier when I merged the two queries and now everything is calculating as it should.
Very strange. I recreated this as a calculated column and got the right answers. Can you confirm that your date columns are tagged as Date columns and not something like text?
Anonymous- One thing that I did differently (I tested with the exact same dates) is that I did not include the ".[Date]" portion when referencing my columns. See if that makes a difference.
Hi,
What result do you get when you write this calculated column formula
=Data[Shipped Date]-Data[PO Date]
