Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Ashish_Mathur
Super User
Super User

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/
MFelix
Super User
Super User

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.