Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Time intelligence does not work properly

Hello all,   I am trying to perform some time intelligence calculations. However I can't seem to get anything to work properly. I have a DateTable, and a SalesHistory fact table. I have a relations...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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.

MFelix's avatar
MFelix
Super User
8 years ago

Hi Anonymous,

 

Replace your measure by:

 

Total Sales =
SUM ( SalesHistory[NetAmount] )

Total Sales MTD =
CALCULATE ( TOTALMTD([Total Sales], DatesTable[Date] ) )

Total Sales YTD =
CALCULATE ( TOTALYTD([Total Sales], DatesTable[Date] ) )

Should work as you need.

 

Regards,

MFelix

 

  • mattbrice's avatar
    mattbrice
    Solution Sage
    8 years ago

    From first look your measures look fine. TOTALYTD is just syntax sugar for way you have already done with DATESYTD. Same for TOTALMTD. 

     

    So something else is causing issue that would require looking at sample file. 

     

    I assume you have looked at fact table and actually have data in 2018? 2017?