cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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! 

 

Capture1.PNGCapture2.PNGCapture3.PNGCapture4.PNG

 

 

 

 

 

 

 

 

Total Sales =
SUM ( SalesHistory[NetAmount] )

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

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

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

6 REPLIES 6
New Member

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.

Super User III
Super User III

Hi,

 

In addition to what's been suggested, please also ensure that you have written them as measures and not as calculated column formulas.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User III
Super User III

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





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?

Anonymous
Not applicable

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.

View solution in original post

Anonymous
Not applicable

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! 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors