Duplicate date showing up twice

[Anonymous]

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?

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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.  

[Anonymous]

It was already on Date only in power bi

[Anonymous]

can you post your file?

[Anonymous]

Unfortunately it has too much confidential data. The weird thing is that Power BI Online shows the data correct. 

[Anonymous]

hm. that is odd... I'm still thinking there is something up with the actual date field. any chance you can just get that column and send as is?  Just hard to trouble shoot without actually seeing something

[Anonymous]

I have two sheets for my data, 1 2017 and 1 2018. Here are the 2 coloumns of dates from that. 

https://www.dropbox.com/s/shyooxfuy6ekf93/Dates.xlsx?dl=0

[Anonymous]

Used your data (added some random sales figures) and I don't get the duplicate in Excel or Desktop. 

So, not sure what to make of that.  I'd just be sure that in Power Query you change the date completed column to Date:

Other than that, no idea what's going on....

[Anonymous]

Thanks for looking into it. Another weird thing is if i go to the data tab in PBI desktop is shows all the dates

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.