CreatingTime Calculations with Power BI

Creating Time Calculations in Power BI

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)

Power BI DAX date table

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.

Power BI gauges & cards

Power BI line chart & area chart

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?

Let me know what you think. Please leave a comment down below.
And if you found this useful, please share it!
Advertisements

5 thoughts on “Creating Time Calculations in Power BI”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s