I am working on building a tabular cube and needed the ability to compare current fiscal year dates to prior fiscal year dates by matching the dates based on the same fiscal month and day of month and then performing calculations to compare sales between the current and prior fiscal year.  The built-in DAX date calculations didn’t appear to meet my needs (at least I wasn’t able to obtain the desired results using them), so I implemented the necessary behavior using an inactive relationship from my date dimension to the sales fact table.

As a first step, I added two additional columns to my tabular cube date dimensions that represents the prior year date key and date.  An example of the query is included below (note: for simplicity in this blog post I have not included all the date dimension attributes, an actual date dimension would likely have many more attributes than what I have included).  One challenge I encountered is an occasional mismatch between the number of days in a given fiscal month when comparing two fiscal years (some fiscal years have more days in a particular fiscal month than the prior year);  therefore, I am using a LEFT OUTER JOIN and then ISNULL to return an unknown for the LastYearDateKey column.  In addition, this column needs to have a unique value so the unknown is set to be –1 multiplied by the prior year date key.

SELECT DateKey, [Date], ISNULL(c.LastYearDateKey,-1*DateKey) [LastYearDateKey],c.LastYearDate [LastYearDate]  
FROM DimDate d  
LEFT OUTER JOIN (SELECT c.DateKey CurrentYearDateKey, l.DateKey LastYearDateKey, l.Date LastYearDate  
                  FROM DimDate l  
                  INNER JOIN (SELECT DateKey,Date,FiscalYear - 1 [FiscalYear],FiscalMonth,FiscalDayOfMonth FROM DimDate) c ON l.FiscalYear = c.FiscalYear   
    AND l.FiscalMonth = c.FiscalMonth   
    AND l.FiscalDayOfMonth = c.FiscalDayOfMonth) c ON d.DateKey = c.CurrentYearDateKey 

Now that I have the necessary columns added to my dimension, I created an inactive relationship from my sales fact table to the new LastYearDateKey column in the date dimension (The primary / active relationship from the date table to the sales fact table is based on the current year date key).  The following shows the inactive relationship between the two tables (note that the active checkbox is unchecked).

With the inactive relationship established, I can create measures using DAX calculations that reference this inactive relationship.

Here’s an example of a measure in my sales table that uses the inactive relationship:

Sales Amount PY:=CALCULATE(SUM([SalesAmount]),USERELATIONSHIP(Dates[LastYearDateKey],Sales[GLDateKey])).

Now, I can compare sales in the prior year to the current year using the custom date matching (based on fiscal month and day of month) defined in my date dimension query.