Forum Discussion
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 relationship between them on Date and InvoiceDate. On my date table, there is one day for every year from 2008 - 2018. In my fact table, I am only pulling data from 2008 - 2018. In the Power BI Desktop, I'm trying to simply start out by calculating Total Sales, MTD Sales and YTD Sales. I created the measures and placed them on Card visualizations and they show blank. I even tried using a filter on year and month and still get no values. What could I be doing wrong? I have pasted all relevent information below.
Thanks to everyone in advance!
Total Sales = SUM ( SalesHistory[NetAmount] )
Total Sales MTD =
CALCULATE ( [Total Sales], DATESMTD ( DatesTable[Date] ) )
Total Sales MTD =
CALCULATE ( [Total Sales], DATESYTD ( DatesTable[Date] ) )
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.
6 Replies
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.
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
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?
Hi,
In addition to what's been suggested, please also ensure that you have written them as measures and not as calculated column formulas.
Have you also marked the date table as a date table, not sure about creating models directly in PowerBi, but Time Intelligence wont work in SSAS unless you mark it first.
