Forum Discussion
Time Intelligence functions and dot notation
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.
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.