cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Solution Sage

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.

3 REPLIES 3
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!

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!

Solution Sage

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.