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