cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brubaker29 Regular Visitor
Regular Visitor

Duplicate date showing up twice

For some reason 12/31/2017 is showing up twice in a chart instead of showing 1/1/2018. The sales number is right but showing the wrong date. The date is coming from the sales order table but the same thing happens if i use the calendar table. Any ideas?

Screen Shot 2018-11-19 at 5.38.20 PM.png

 

 

 

16 REPLIES 16
Super User
Super User

Re: Duplicate date showing up twice

I'd be expecting that the answer lies within the FiscalDay data.  My expectation is that you have data where FiscalDay is 365 and other data where FiscalDay is 1 for that same date.


   

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

Proud to be a Datanaut!


   


brubaker29 Regular Visitor
Regular Visitor

Re: Duplicate date showing up twice

But it shows up wrong too if I just use my order table date and net sales. Also it shows up correctly online when published.
Super User
Super User

Re: Duplicate date showing up twice

Can you post some sample data, perhaps some can take a look at it.  Its going to be very hard to speculate from the details so far.


   

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

Proud to be a Datanaut!


   


brubaker29 Regular Visitor
Regular Visitor

Re: Duplicate date showing up twice

Sorry for the late response. The weird thing is if I just use my order data table (no calendar table) and look at it in the desktop and published to the web it is different.

Screen Shot 2018-12-05 at 9.20.18 AM.pngScreen Shot 2018-12-05 at 9.20.27 AM.png

brubaker29 Regular Visitor
Regular Visitor

Re: Duplicate date showing up twice

Another weird thing is when i export to excel from desktop it shows a time of 11pm for the 12/31/17 that should be 1/1/18

Screen Shot 2018-12-05 at 1.40.22 PM.png

Super User
Super User

Re: Duplicate date showing up twice

That would suggest to me that you do indeed have some differences in your DateTime data.

 

Dates are usually stored as a whole number, in a format of Days since 31/12/1899.  Time is usually stored as a decimal, where 0.5 would be midday.

 

I'd be wondering if one of your dates isn't a whole number and is thus coming up as the 11pm.  Something like 43000.96 rather than 43001.


   

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

Proud to be a Datanaut!


   


Nick_M Senior Member
Senior Member

Re: Duplicate date showing up twice

If you transform the date data type to just Date in Power Query, it should work.  Since there are different times at then of our dates they are actually two different periods, even if we format as days.  What we see doesnt always match what is actually there.  

brubaker29 Regular Visitor
Regular Visitor

Re: Duplicate date showing up twice

I converted all the numbers in excel for 1/1/2018 and they were all 43101.00

Nick_M Senior Member
Senior Member

Re: Duplicate date showing up twice

did you try to do any transformations in power query to ensure all the dates are dates and nothing hanging on?