Basic date handling in DAX
One of the things that you learn very quickly in the tabular model is that you always need to have a date table. I thought that for my Hans Rosling project I could get away without one, since the data is captured only annually. Surely you should be able to do year-over-year calculations and get the previous year in DAX from the row context, right? Wrong. After much trial and error (read: error), I made the date table and got the calculations to work.
There’s a couple of things to note about the date table. If your date requirements are simple enough (and mine are as simple as you can get), there is no need to have a date table in the data source. You can just construct it in a SQL query. Also, your date key cannot be a calculated column. In my case, it was therefore necessary to manufacture a key as part of the table definition and use it when marking the date table.
The SQL for my table definition is indeed very basic:
select distinct d.Year, DateKey=CAST(DATEFROMPARTS(d.Year, 1, 1) AS DATE) from dbo.DemographicFacts d
The next step is to explicitly mark this table as a date table. You can find the “Mark as Date Table” easily enough in the PowerPivot ribbon. In SSDT (formerly BIDS), you can find this feature in Table->Date->Mark as Date Table. In the dialog box that appears, select DateKey, OK.
A few more modeling tasks remain. Hide the DateKey column by right-clicking it and selecting “Hide from Client Tools”. Finally, create the relationship between the date table and the Demographic Facts table. You can create the relationship on the Year column with no ill effects. The time intelligence functions still work.
Now, to write the DAX formulas. My task was to fetch the data for the previous year so I could do a year-over-year calculation. In my previous post on learning DAX I linked to this post on using time intelligence functions. Armed with the information on that page, I could solve my problem very simply using the DATEADD() function.
For example, to calculate the year-over-year growth in Average GDP Per Capita, I defined these measures:
Avg GDP Per Capita:=AVERAGE('Demographic Facts'[GDP Per Capita]) Avg GDP Previous Year:=CALCULATE([Avg GDP Per Capita], DATEADD('Date'[DateKey], -1, YEAR)) YOY Growth GDP:=IF([Avg GDP Per Capita]&&[Avg GDP Previous Year], ([Avg GDP Per Capita]-[Avg GDP Previous Year])/[Avg GDP Previous Year], BLANK())
And there you have it, basic date/time intelligence. Unfortunately in performing these calculations I found that the data did not support my hypothesis (that there would be a relationship between GDP growth, growth in life expectancy, and reduction in fertility). Unfortunately DAX won’t help me make better hypotheses, I still have to do that sort of thinking myself. On to guessing other relationships and finding more supporting data…
From → BI Semantic Model