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
samwong
Frequent Visitor

Time Intelligence functions and dot notation

Hello, newbie here but I could not find any relevant information in the forums (perhaps my search terms are wrong).

 

I am confused with using dot notation in the time intelligence functions.  Perhaps dot notation is not the correct term, but I come from a OOP background so please excuse.

 

I managed to calculate the sum of a previous year using:

countPY = calculate(
sum(Data[Count]),
PREVIOUSYEAR(Data[DATE].[Date])
)

 

I would like to understand why I had to use PREVIOUSYEAR(Data[DATE].[Date]), and not just PREVIOUSYEAR(Data[DATE])

 

From reading the DAX Reference it sounded to me that the PREVIOUSYEAR() function would intelligently parse out the year from the date.  Why did I have to explicitly add .[Date] to the expression?  (The calculation does not work when  .[Date] is removed.)

 

(the TRAP_DATE column is categorized as a Date field and not a String field.)

1 ACCEPTED SOLUTION

Time intelligence functions work by shifting around the "window" of dates that are visible in the filter context, not by performing math on the Data[Date] column to "create" values that don't already exist in the column.  So the dates must exist for the functions to work properly.  In your example the PREVIOUSYEAR() function call will return all the dates from the previous year, given the last date visible in Data[Date] column - but only if they exist.  PREVIOIUSYEAR () as called will basically do this:

 

FILTER ( ALL ( Data[Date] ), YEAR ( Data[Date] ) = YEAR ( MAX ( Data[Date] ) ) - 1 ) 

So one reason to use a separate Calendar table is to be sure you have all contiguous dates needed.  Once you have studied up on Dax you'll realize having a separate Calendar table with full year, contiguous dates is a best practice.  

View solution in original post

3 REPLIES 3
Sean
Community Champion
Community Champion

I believe the .[Date] allows you to perform the Time Intelligence functions when you don't have a Calendar Table in the Data Model.

 

I'm not sure if all functions are supported with the dot notation though...

 

Anyway if you did have a Calendar table and had Data[Date] related to Calendar Table[Date] then you would not need the .[Date]

 

you can test by clicking New Table and type Calendar Table = CALENDARAUTO( )

 

Hope this helps! Smiley Happy

samwong
Frequent Visitor

Thank you for your reply.  I will have to play around more with PowerBI (just started last week) to understand the use of a calender table.  Not sure why I would need to create a separate table when I already have a column with dates.

 

Cheers!

Time intelligence functions work by shifting around the "window" of dates that are visible in the filter context, not by performing math on the Data[Date] column to "create" values that don't already exist in the column.  So the dates must exist for the functions to work properly.  In your example the PREVIOUSYEAR() function call will return all the dates from the previous year, given the last date visible in Data[Date] column - but only if they exist.  PREVIOIUSYEAR () as called will basically do this:

 

FILTER ( ALL ( Data[Date] ), YEAR ( Data[Date] ) = YEAR ( MAX ( Data[Date] ) ) - 1 ) 

So one reason to use a separate Calendar table is to be sure you have all contiguous dates needed.  Once you have studied up on Dax you'll realize having a separate Calendar table with full year, contiguous dates is a best practice.  

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.