# How To Do Trend Analysis In Power BI Using DAX

Showing trends and how they change over time can add a lot of value to your consumers and decision makers. In this post, I will show how to use conditional formatting inside Power BI tables to showcase this insight really well. Obviously to show a trend analysis in Power BI, we actually need to calculate growth rates. You may watch the full video of this tutorial at the bottom of this blog.

This is also a great use of the DAX formula language. I will show you how you can use a combination of formulas, including time intelligence calculations, to come up with the correct results.

I will also show a quick way to discover which products are performing well and which are not over a certain time frame. Sometimes you just want to see what is currently trending in your sales, and identify it right away. Other times, you want to identify these trends on a longer time frame.

I’m going to show you how to do a trend analysis in Power BI very quickly on a quarterly basis and then transition to a monthly basis. First, we start with Total Sales. We need to identify what is the growth from one period to the next and look at it from a quarterly basis. We will create a measure and get the sales per quarter in a relatively straightforward fashion. All we have to do is create a quarterly context by dragging in the Quarter & Year column and associating it with the Total Sales column.

But what if we wanted to see what was the growth quarter from the current quarter to the quarter before? For example, what was the difference between Q3 2015 and Q2 2015? If you were able to see that these numbers were all negative, then you can identify a downward moving trend in your sales and actually do something about it.

## Getting Quarterly Sales Growth

To achieve this, we need to add a bit of logic here. We will create a table and call it Quarterly Sales Growth, divide our Total Sales, use the CALCULATE function, and then add the DATEADD function to jump back a quarter. We will then put in the alternative result of 0, and then add -1 to get the percentage growth.

Lastly, we put the formula into percent and then drag it into the canvas. You can now see we’re getting percentage growth month on month. As you can see that on Q3, we’re down 8.09% from Q2.

## Getting Product Growth

But what if we want to identify and see the trends in specific products? The first thing we need to do is get rid of Sales and turn this into a matrix. Then we will find our Products and put it in Rows. We can now see the growth per product for every single quarter.

## Using Conditional Formatting

So now we can see the results, which is great. But just by looking at it, I can’t really tell anything quite honestly. So how do we make this visually compelling, or change it in such a way that we can quickly tell what is going on? There’s a feature in Power BI called Conditional Formatting.

When we click on this, it will bring up the Conditional Formatting box. We just put in a number on the Minimum and Maximum fields and assign a colour to them.

Once we click on OK, we can see how our products are faring quarter after quarter and determine if they are trending up or trending down.

## Tracking Trends On A Monthly Basis

We can do this on a monthly basis as well. We just copy and paste the table and measure we are working on. Then we are going to change the title from Quarterly Sales Growth to Monthly Sales Growth, and then change what DATEADD is qualifying from Quarter to Month.

Then we’ll grab the Month and Year numbers to our table, change the format to percentage, and apply Conditional Formatting as well.

Let’s say we work on a retail store or a supermarket. We are selling a number of things constantly every single day and we need to identify trends very quickly, so this is going to be useful. If there are lots of negative numbers, then we have a problem in our hands and need to figure out what’s going on with these particular products.

On the other side of the coin, if the numbers are going up, then we can figure out what we’re doing right with these products and see if we can apply them to those that are not doing so well. Think about how you can use this trend analysis in Power BI in different ways, not just in sales.

Using Moving Averages To Showcase Trends in Power BI
Showcasing Deviations & Trends With Power BI Using DAX
Analyze & Showcase Quarter On Quarter Sales Trends In Power BI

## Conclusion

There’s a little bit of DAX in this tutorial, so if you’re just starting out with formulas inside of Power BI check out Mastering DAX Calculations at Enterprise DNA Online. This course covers all the ‘need to knows’ around DAX. Using DAX effectively is extremely important in Power BI. Any quality analytical insight and trend analysis in Power BI can be achieved by implementing DAX well and having a data model which is built correctly with the right relationships.

The tables inside Power BI are fantastic. They are so much more than just a static table of information. Firstly, they’re dynamic, so they’ll show your results based on the filters that are applied to your reports. They also have some good conditional formatting options, but they can also be used as a filter inside your report. So many options and opportunities to use these effectively to showcase great insights.

I always like to use tables to show granular levels of detail. You might have your bar or column chart aggregate information up, but then you may also want to see every single sale or transaction and examine the details of that. Tables are perfect for this.

This example does actually show the matrix visual, but tables are very similar, and all similar techniques can be applied across both from a visualization perspective. Place a comment below if you have any thoughts or feedback.

## Building a Data Visualization Portfolio – Showcasing Your Skills and Insights

Data visualization is a powerful tool that allows you to communicate complex information in a way that...

## Guide to Intermediate DAX Functions for Power BI

In today’s data-driven world, being able to use data analysis expressions (DAX) in Power BI and other...

## Optimizing DAX: Performance Tips for Power BI Reports

Data analysis in Power BI is not only about creating visually appealing reports but also about ensuring...

## DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.

## Introduction to the RANKX and TOPN DAX functions in Power BI

One of the key aspects of analyzing data is to be able to rank or compare different elements based on...

## Power BI Visualization Technique: Learn How To Create Background Design Plates

Here's a Power BI visualization technique that you can utilize within your reports by using large...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculate Average Per Customer Transaction Using DAX In Power BI

What I want to demonstrate in this tutorial is how we can calculate average sales, profits, or...

## Round Bar Chart – A Visualization For Your Dashboard

In this tutorial, you’ll learn how to create round bar charts for your Power BI report. It’s another...

## Conditional Formatting with Transparency Hex Codes

One of the best ways to implement conditional formatting is using the Field value option in the...

## Custom Theming In Power BI

For today's post, I'll do a quick review of the customizations you can make by directly editing and...

## Power BI Automation With Elgato Stream Deck

Today, I'm going to walk you through an outstanding Power BI automation using Elgato Stream Deck. We're...