One of the current issues in Power BI is the inability to specify a Date table. The Date table is what enables us to create some of the powerful DAX time calculations like Year To Date, Month To Date and more when the Date key is not a Date data type. Ginger Grant has blogged about this issue with a proposed work around, which you can read about here. Even though we can’t exactly specify which table is our Date table in Power BI, that doesn’t mean we can’t create some nifty time calculations with some clever DAX. Also, these same calculations can be used in Power Pivot and SSAS Tabular models, as well.

To get started, I added a few calculated columns to my table that contains my dates. I created a Relative Year column that returns a number indicating the relative position based on the current year, a Current YTD that displays a 1 for all days in this year less than or equal to the current date a Current MTD column that shows 1 for all days in the current month when the date is less than the current date.

Relative Year = YEAR(now()) - [CalendarYear]
Current YTD = if(DimDate[CalendarYear] = YEAR(NOW()) && [Datekey] <= NOW(),1,0)
Current MTD = if(DimDate[CalendarYear] = YEAR(NOW()) && [CalendarMonthNumber] = MONTH(NOW()) && [Datekey] <= NOW(),1,0)

Now that I’ve created the calculated columns that are dynamic, I can create calculated measures based on my date calculation columns. Here are a few examples:

Last Year Total Sales = CALCULATE([Total Sales Amount],'DimDate'[Relative Year]=1)
This Year Total Sales = CALCULATE([Total Sales Amount],'DimDate'[Relative Year]=0)
2 Year Ago Total Sales Amount = CALCULATE([Total Sales Amount],'DimDate'[Relative Year]=2)
Current Year Sales Growth = [This Year Total Sales]-[Last Year Total Sales]
Current Year Sales Growth % = DIVIDE([Current Year Sales Growth],[Last Year Total Sales])
Current YTD Total Sales Amount = CALCULATE([Total Sales Amount], 'DimDate'[Current YTD] = 1)
Current MTD Total Sales Amount = CALCULATE([Total Sales Amount], 'DimDate'[Current MTD] = 1)

With the calculated measures in my Power BI model, I can create some pretty nifty gauges, cards and other types of key performance indicators.

I hope you found these tips useful for creating some nifty time calculations in your Power BI for date and time analysis.

Update

Some friends on Twitter ( Tom & Konstantinos) have correctly pointed out that you can leverage the DAX time intelligence functions in Power BI as long as the key between the fact table and the date table is a date datatype. If your key is some other data type, like Integer in the case of the Adventure Works data warehouse, you won’t be able to use the DAX time intelligence functions. In that particular case, in order to use the DAX time intelligence functions you’d need to convert your keys to date values and then the date functions should work. -DR 9/15/15

Power BI Time Calculations Resources

Marco Russo and Alberto Ferrari have put together an incredible resource that includes loads of time calculations that can be created without specifying a Date table. You definitely need to check this one out.

Feedback?