DAX time intelligence functions are widely used in Power BI. Time intelligence is the most typical type of analysis you do. That’s why DAX has a section of calculations specifically catered toward ‘Time Intelligence’. Seeing calculations and comparing calculations over time is very common no matter what industry or function that you work in.
I thought it would be a good idea to run through a recent Power BI partner showcase submission where I have used a number of techniques to show results over time.
We have a simple example of an organisation that sells something. We have information on the customer, products and regions where sales are occurring.
In the first page of the report, we can view our key metrics in many different ways. Those key metrics can include such calculations as total revenue, costs, profits and transactions.
Here we are also viewing sales and profits cumulatively.
What’s great about this demo is that you can dynamically filter any time frame and have all visuals in the page update accordingly. Even the cumulative calculations will reset only for the time frame selected.
How do we do that with the cumulative totals? Below is the DAX code for the cumulative total of sales. Using the ALLSELECTED function means that it will reset the calculation from the first day in our selection to the last day only.
This is a great pattern that you will use time and time and again.
This also updates the cumulative calculation if you selected another dimension in your data. Here I am selecting a city in Australia and we can review how total sales cumulatively have performed against last year sales.
So within this one report page, we can get an enormous variety of comparisons. We can dynamically select our time frame and then even drill into a particular city on our map. All of our time intelligence calculations will update automatically based on the selection.
Lastly, we will touch on the smaller sparklines that sit near the top of the page. These are representing the card information over time (total sales, profits and transaction). To remove some noise, I decided to use a moving average calculation as that would smooth the result somehow and enable us to see any trends emerge.
*****Related Links*****
Common Time Intelligence Patterns Used In Power BI
The DATEADD Function: The Best And Most Versatile Time Intelligence Function In Power BI
Time Intelligence In Power BI: How to Calculate The Number of Transactions Made in the Last N Days
This is the DAX code to get a moving average calculation through time. I’ve used a 7 day moving average here.
I like to use this type of analysis when a chart through time starts to look too busy with many ups and downs. Sometimes all you need to identify a trend is to place a simple moving average calculation over the underlying data.
Check out more from this showcase on the Power BI partner showcase site. Should be out soon.
All the best!
Sam
