cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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: 

 

Capture.JPG

 

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
danextian New Contributor
New Contributor

Re: DATEDIFF incorrectly calculating

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 DateShipped DateDays to Ship-incorrectDays to Ship-correctNumber of Rows?
2/7/20182/28/2018105215
1/17/20182/23/201874372
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

12 REPLIES 12
Super User
Super User

Re: DATEDIFF incorrectly calculating

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?

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Anonymous
Not applicable

Re: DATEDIFF incorrectly calculating

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.
Super User
Super User

Re: DATEDIFF incorrectly calculating

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Anonymous
Not applicable

Re: DATEDIFF incorrectly calculating

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

Super User
Super User

Re: DATEDIFF incorrectly calculating

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: DATEDIFF incorrectly calculating

Hi,

 

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

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
danextian New Contributor
New Contributor

Re: DATEDIFF incorrectly calculating

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 DateShipped DateDays to Ship-incorrectDays to Ship-correctNumber of Rows?
2/7/20182/28/2018105215
1/17/20182/23/201874372
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

Anonymous
Not applicable

Re: DATEDIFF incorrectly calculating

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

Microsoft v-yulgu-msft
Microsoft

Re: DATEDIFF incorrectly calculating

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,272)