Search this blog:

Search This Blog

DAX Time Intelligence - Easy pattern to get DAX Time Intelligence functions to return the correct results

 DAX is a simple but complex language. It can be very powerful if you understand how to use it properly. In this post, we're going to look at a specific group of functions in DAX: Time Intelligence.

Time Intelligence DAX functions

Time Intelligence functions in DAX change the filter context on the Date table of your model. 

Step 0: Prepare 

Before using Time Intelligence functions, you should:

Time Intelligence functions in DAX are not vital functions. We can calculate the same results without their use, just need to think a bit differently about the problem. Greg Deckler has a great post on this here:

My post here aims to debunk the mysteries behind using Time Intelligence functions and provide you with an easy pattern that works every time.

Easy Time Intelligence DAX Pattern

Now that we know what time intelligence is, how do we use it?

Step 1: Create a base measure

You may have already completed this step. The base measure is what you are reporting on, it should come from a table related to your Date table. Consider the 'Example 2' in my post on Relationships in Power BI: 

We have a 'Date' dimension table and an 'Incident Reports' fact table. These are the only two tables we need to consider for this solution. 

Our base measure will be Incident Count:

Incident Count = DISTINCTCOUNT( 'Incident Reports'[IncidentID] )

Your base measure could be anything, and use any aggregate function or filters, or combination of DAX functions, as long as it returns the results you expect when put in a table/matrix with a column from your Date table. 

Step 2: Select your time intelligence function

This may be the most difficult step, and I may write a separate blog on this if I get time. For this post, I'm going to use my favorite time intelligence function: DATEADD. I like this function because it's versatile and works for a lot of the scenarios my clients ask for, without requiring me to learn /memorize new functions.

For this example, I want to compare my [Incident Count] base measure above to the same period last year. DATEADD will work nicely for this.

Step 3: Apply the Time Intelligence Pattern

Now we're ready to apply the easy Time Intelligence Pattern. 

Time Intelligence Pattern Measure = CALCULATE ( [Base Measure], FUNCTION ( 'dimDate'[Date], functionparameters ) )

What does this look like in practice? Let's apply it to our example using DATEADD and [Incident Count]: 

Incident Count Prior Year = CALCULATE ( [Incident Count], DATEADD( 'Date'[Date], -1, YEAR) )

Step 4: Use in the context of your Date table

This final step is critical, and one that can cause many well-written time intelligence functions to fail if forgotten. You MUST use this measure in the context of your Date dimension table. 

In my example, I've created an [Incident Count Prior Year] measure. If I don't put a filter for year, or use this in a table / visual split by Year, I'll just get the result of all Incidents except the maximum year in my date table (this is by design and how time intelligence functions work, something that I may expand on in another blog post). 

For many people, their date tables include future years (for budgeting and forecasting purposes), so when they create a time intelligence function correctly, it simply returns the same value as the base measure. That is, until they apply a date filter.

Use the Date dimension in any visuals or filters for this measure. 


That's it! You've now got the easy time intelligence pattern for DAX. It can be applied to all sorts of different scenarios. 

Additional Examples

Total Sales = SUM( Sales[Sales Amount] )

Total Sales Prior Year = CALCULATE( [Total Sales], DATEADD( dimDate[Date] , -1 , YEAR) )

Total Sales YTD (calendar) = CALCULATE( [Total Sales], DATESYTD( dimDate[Date] )

Total Sales YTD (fiscal) = CALCULATE( [Total Sales], DATESYTD( dimDate[Date], '6-30' )


Key tips:

  • Create a base measure first - it makes things easier to follow
  • Use the CALCULATE function with the Time Intelligence function as a filter
  • Use the Date dimension table in:
    • Your time intelligence function wherever it asks for a date
    • Your visuals and filters whenever using the new measure

Get Link to Channel: Private Teams Channel

 I've just struggled with trying to 'Get link to channel' for a Private Channel in Teams. I typically recommend against using private channels in Teams, and perhaps this is one reason. 

Not sure why, but the 'Get link to channel' functionality doesn't exist for Private Channels like it does for Public Channels. 

Even more frustrating, when you do finally find the link to the Private Channel, it doesn't work for a button embedded in a SharePoint page within Teams tab.

Luckily, with a few simple deletions, you can get this to work.

Step 1: Click the three dots at the top right of Channel 'Posts'

Public Channel Menu:

screenshot Public Channel Menu

Private Channel Menu:

screenshot Private Channel Menu

For the Public Channel, your job is done - click the 'Get link to channel' option and copy the link. It will look something like this:

Get Link to Channel Link

For the Private Channel, click 'Open in SharePoint' first, then click the Teams icon (you won't be able to right click > copy link, so will have to actually click the link to open. This will open a brower tab initially, with an option to open Teams - cancel the dialog box. 

Copy the URL from the web browser. It will look something like this:

Open in Teams Link

Step 2: Edit link

We want to make this link follow the same format as our first 'Get link to Channel' link. Let's compare them side by side: 

Get Link to Channel Link

Open in Teams Link


You'll notice the 'Get Link to Channel' link is just a simpler version of the 'Open in Teams' link. To get the 'Open in Teams' link to work from SharePoint page embedded within Teams tab, simply delete the parts in bold below:

to end with a link that resembles the 'Get Link to Channel' link:

Meaningful Power BI titles using DAX

Today's post is a short one with a video to demonstrate what I have learned. I'm a big believer in providing your end user with as much information as possible. Someone new should be able to pick up the report and understand it without needing to get a degree in Power BI or go digging through your company's intranet to learn what's what. 

One of the neat features that Power BI provides, is the ability for any visual to act as a filter or 'cross-filter' to other visuals. This provides us with comparative values and highlighted bars, but Power BI tooltip just says 'highlighted', which isn't very helpful.

screenshot Power BI cross filter

So, I wanted to create a dynamic title that showed the user what exactly was 'highlighted' as well as what that 'sum of value' that the 'highlighted' was compared to.

You can see how I did it in the video, but the DAX is below:

Title =
VAR _Cat =
    IF (
        ISFILTERED ( Items[Category] ),
        SELECTEDVALUE ( Items[Category] ),
        "All Categories"
VAR _Item =
    CONCATENATEX ( Items, Items[Item], " & " )
    IF ( ISFILTERED ( Items[Item] )_Item & " compared to " & _Cat_Cat )

The basic trick is to use ISFILTERED to figure out if the user has actually filtered by that column, or only by another column. The next trick is to design the report so that the user doesn't accidentally filter by the wrong column in the wrong place.

Custom Visual Review: Charticulator

This is not your ordinary custom visual - this is EVERY custom visual. Charticulator puts the power to design and develop custom visuals to ...