Forum Discussion
Time intelligence does not work properly
First of all, great job providing the community with the tools needed to help you troubleshoot!
My best guess is that your SalesHistory[InvoiceDate] is actually a datetime data type instead of just date.
If you have a time portion in the date, it's highly unlikely that the InvoiceDate will match your calendar table...1/1/2018 5:03:14 AM is not the same as 1/1/2018 12:00:00 AM (which is what your calendar date will be).
Don't just change the data type in Query Editor. I've run into instances where this step didn't actually truncate the time properly.
Highlight the [InvoiceDate] column, click on the transform tab, click on the highlighted Date button, and click "Date only".
That should truncate the time portion off.
First of all, great job providing the community with the tools needed to help you troubleshoot!
My best guess is that your SalesHistory[InvoiceDate] is actually a datetime data type instead of just date.
If you have a time portion in the date, it's highly unlikely that the InvoiceDate will match your calendar table...1/1/2018 5:03:14 AM is not the same as 1/1/2018 12:00:00 AM (which is what your calendar date will be).
Don't just change the data type in Query Editor. I've run into instances where this step didn't actually truncate the time properly.
Highlight the [InvoiceDate] column, click on the transform tab, click on the highlighted Date button, and click "Date only".
That should truncate the time portion off.
Sorry it took so long to reply. But this worked perfectly. I had changed the type, but not under the transform option. Thank you very much. Has saved me much frustration!