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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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





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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

13 REPLIES 13
danextian
Super User
Super User

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





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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

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.

 

 

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

I would also like to know what the resolution is.

Check "Dont Summarize" and it works!!

Anonymous
Not applicable

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

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

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/
Greg_Deckler
Super User
Super User

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?

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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