DATEDIFF incorrectly calculating

[Anonymous]

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! 

[danextian · Accepted solution]

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

[Anonymous]

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.

[mwen90]

Hey, 

I am having this exact issue. How did you resolve it, I am a little confused as to the issue/resolution?

@Nolock this seems to the same issue?

Cheers

[Jadnerb]

I would also like to know what the resolution is.

[Satya01]

Check "Dont Summarize" and it works!!

[Anonymous]

I am sorry for the delayed response. I will check that and get back to you on if that solves the issue.

[v-yulgu-msft]

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

[Ashish_Mathur]

Hi,

What result do you get when you write this calculated column formula

=Data[Shipped Date]-Data[PO Date]

[Greg_Deckler]

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]

Yes, just reconfirmed that the columns are tagged as date columns. Also doubled checked in query editor as well and all my date columns are correctly tagged as date columns. What is strange is that it’s calculating correctly 95% of the time.

[Greg_Deckler]

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

[Anonymous]

I removed the ".[Date]" and unfortunately that doesn't solve the issue.

[Greg_Deckler]

That's a weird one, especially since I can't recreate with the same dates. If you have a Pro account, open a support ticket.