Time Patterns For Power BI - 4 Examples Of Time Intelligence

by EnterpriseDNA Regular Visitor on ‎08-29-2018 09:03 AM

In this detailed post, I wanted to focus on time patterns.

 

I will show you four different examples of ways you can utilize time intelligence functions effectively inside Power BI. Hopefully, by going through these examples you'll see the versatility of this great grouping of DAX functions available to us inside Power BI.

 

The Microsoft development team have seriously made any time-related analysis extremely effective and intuitive for us. All we need to understand are the fundamentals around how the DAX formula language works. Things like 'context' and the difference between 'row' and 'filter' context.

 

For now, I want to focus on these four techniques and combinations of time intelligence functions that you can use to showcase interesting insights.

 

Let me start with an easy function - DATEADD. This is my favorite time intelligence function because it is the best to use if you will make a comparison over time. 

 

When I say comparison, a good example is comparing today's result to last year or last month. DATEADD just makes it easy to move in and out of any comparison you want to achieve in your analysis.

 


The next example is around using the time intelligence functions for MTD, QTD, and YTD.

 

If we had to write these formulas out in full inside Power BI they would be a lot more complex. This is why it is totally recommended to use the time intelligence functions.

 

All you have to understand well is how to use these inside of CALCULATE. A good understanding of the CALCULATE function is crucial. If you understand CALCULATE, which by the way is the most important function in the entire DAX language to understand well, then implementing this in your analysis is not that difficult.

 


The next example I want to cover is rolling totals.

 

To achieve a rolling total inside of Power BI, we need to utilize a slightly different pattern inside our formulas. We will still use the CALCULATE function but inside that, we will use a different range of filters to achieve an on-going rolling total, so for whenever we get to a particular day, we want to be aggregating up a certain amount of days to get our rolling total.

 

There are lots of application for this. The great thing about rolling totals is you can branch out from it and you can start completing comparisons of your rolling totals utilizing time intelligence functions. It's a really powerful stuff from an analytical perspective.

 


In this last example, I want to show you how you can get a little trickier with time intelligence functions to find insights that are quite unique and advanced.

 

I will show you how you can look at your current sales and compare those to your previous best month. So you're not just comparing to any particular month or year, instead, you're looking back through time to find the month with the best sales and compare it to your current sales.

 

To achieve this requires a combination of formula techniques and some solid understanding of context.

 

I covered this in detail in this particular example. It is absolutely worth reviewing and understanding deeply because there are many ways you could rearrange these to achieve other quite unique results and calculations.

 

 

The idea around this deep dive into time patterns for Power BI is to showcase the potential of these functions. This is just the start and there are a lot more ways you can do to analyze information and trends over time.

 

 

Good luck!

Sam

 

**** Learning Power BI? ****

FREE COURSE - Ultimate Beginners Guide To Power BI

FREE COURSE - Ultimate Beginners Guide To DAX

FREE - 60 Page DAX Reference Guide Download

FREE - Power BI Resources

Enterprise DNA Membership

Enterprise DNA Online