One year ago I wrote an article describing how the time intelligence DAX functions work in Power BI (I just updated the article including the example described below). In a recent event in Sydney I observed a strange (or at least unexpected) behavior of Power BI during a demo, and I think it is interesting to share a few considerations about it.
As you can read in the article linked above, I wrote that when you apply a filter to the date column of a table, and this column is used in a relationship, then the table is considered a Date table even if it is not marked as a Date table (you cannot do that in Power BI). During my demo, the Date table had two relationships: one with Sales using an integer column, and one with Purchases using a date column. My expectation was that when I was using a calculation involving only Date and Sales tables, a time intelligence filter would have required an ALL ( Date ) condition in the CALCULATE statement, but this was not the case. Let me show this with an example.
Consider this formula:
SalesYTD1 := CALCULATE ( SUM ( Sales[Amount] ), DATESYTD ( ‘Date'[Date] ) )
If the relationship between Sales and Date is made using an integer column in Power BI, I would expect the formula to not provide any YTD calculation unless I rewrite it as:
SalesYTD2 := CALCULATE ( SUM ( Sales[Amount] ), DATESYTD ( ‘Date'[Date] ), ALL ( ‘Date’ ) )
However, SalesYTD1 was working during my demo, despite I had a relationship based on a DateKey column that was an integer value (such as 20170222). After some investigation, I realized that the other relationship connecting Purchases and Date, even if not used in the two measures above, was enough to consider the Date column as a primary key of the Date table, and this produces an automatic addition of the ALL ( ‘Date’ ) function in the CALCULATE statement when a filter is applied to ‘Date'[Date].
Thus, the lesson is that even if you cannot have Mark as Date table in Power BI, you can obtain the same result by creating an hidden empty table with a column Date in the data model, creating a 1-to-many relationship between Date[Date] and the table you created. For example, you can use MarkAsDateTable=FILTER(CALENDARAUTO(),FALSE) to obtain such a table and then create the relationship and hide the table.
You can download a working version of this example in the updated demo of the article Time Intelligence in Power BI Desktop
Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
ALL ( [<TableNameOrColumnName>] [, <ColumnName> [, <ColumnName> [, … ] ] ] )
Evaluates an expression in a context modified by filters.
CALCULATE ( <Expression> [, <Filter> [, <Filter> [, … ] ] ] )
Adds all the numbers in a column.
SUM ( <ColumnName> )
Returns a set of dates in the year up to the last date visible in the filter context.
DATESYTD ( <Dates> [, <YearEndDate>] )
Returns a table that has been filtered.
FILTER ( <Table>, <FilterExpression> )
Returns a table with one column of dates calculated from the model automatically.
CALENDARAUTO ( [<FiscalYearEndMonth>] )