Power BI Spike Analysis
Table of Contents
Introduction
Understanding sales trends is very important in analyzing business performance. Detecting exceptional growth patterns or spikes in sales is the core for business key performance indicators. In Power BI, advanced DAX functions allow us to go beyond simple totals and averages by introducing dynamic calculations based on thresholds and time intelligence. Learning how to visualize these trends effectively can make your reports more insightful. You can learn more: How to Add a Trendline in Power BI.
In this tutorial, you will learn how to analyze monthly sales using three important insights:
- The threshold line is calculated as 75% of the maximum value of monthly sales.
- High Months count how often do sales exceed this threshold.
- Spikes detect transitions where sales increase the threshold compared to the previous month.
By combining measures, variables, and time intelligence functions like PREVIOUSMONTH(), you can build a powerful analytical model to detect meaningful changes in sales behavior.
You can download the Power BI Spike Analysis file through the link located below the blog post.
Setting the threshold:
- Right click on the All Measures table in the Fields
- Click on New measure.
Measure tools open.
- Type the following formula in the formula bar and press Enter.
DAX
Threshold (CM) =
CALCULATE (
VAR MinSales =
MINX ( ALL ( ‘Date'[Full Month] ), [Total Sales Amount (CM)] )
VAR MaxSales =
MAXX ( ALL ( ‘Date'[Full Month] ), [Total Sales Amount (CM)] )
VAR Boundary = MinSales + ( MaxSales – MinSales ) * 0.75
RETURN
Boundary,
ALL ( ‘Date’ )
)
Finding the High Months:
4. Right click on the All Measures table in the Fields
5. Click on New measure.
Measure tools open.
6. Type the following formula in the formula bar and press Enter.
DAX
High Months (CM) =
VAR Threshold = [Threshold (CM)]
VAR MonthsOverThreshold =
FILTER ( ALL ( ‘Date'[Full Month] ), [Total Sales Amount (CM)] > Threshold )
VAR Result =
COUNTROWS ( MonthsOverThreshold )
RETURN
Result
In the expression above:
- The High Month Sales are found by finding the number of months when the sales were greater than the Threshold.
- The Threshold variable stores the value evaluated from the measure.
- MonthsOverThreshold filters all the months where the total sales amount is greater than the Threshold value and stores these month dates in a virtual table.
- At the end, the rows in the MonthsOverThreshold table are counted.
7. Click on the card visual in the visualization pane.
8. Drag and drop Threshold (CM) from All Measures to the Fields.
9. Click on the card visual in the visualization pane.
10. Drag and drop High Months (CM) from All Measures to the Fields.
11. Click on the Slicer in the visualization pane.
12. Drag and drop Category from Products to the Fields.
13. Click on the line chart visual in the visualization pane.
14. Drag and drop Year, Month Name and Date from the Date table to the Axis.
15. Drag and drop Total Sales Amount (CM) and Threshold (CM) from All Measures to the Fields.
16. Click on Bikes in the slicer.
Using PREVIOUSMONTH():
Finding the Spikes:
17. Right click on the All Measures table in the Fields
18. Click on New measure.
Measure tools open.
19. Type the following formula in the formula bar and press Enter.
Spikes (CM) =
VAR Threshold = [Threshold (CM)]
VAR Max Spikes =
FILTER (
VALUES ( ‘Date'[Full Month] ),
VAR Sales Current Month = [Total Sales Amount (CM)]
VAR Sales Previous Month =
CALCULATE ( [Total Sales Amount (CM)], PREVIOUSMONTH ( ‘Date'[Date] ) )
VAR Entering Threshold = Sales Current Month >= Threshold && Sales Previous Month < Threshold
RETURN
Entering Threshold
)
VAR Result =
COUNTROWS ( Max Spikes )
RETURN
Result
In the expression above:
- Spikes specify the number of times that the sales trend crossed the threshold line.
- The Threshold variable calculates the threshold based on the incoming filter context.
- In the Max Spikes variable, a virtual table containing the months of the Spikes is stored.
- Inside the Max Spikes variable, VALUES() generates the unique month table, and Sales Current Month stores the sales for the month in the current filter context.
- Sales Previous Month calculates the sales for the last month using PREVIOUSMONTH() function.
- The Entering Threshold variable checks if the sales for the current month are greater than threshold and the sales for the previous month are less than threshold, i.e., there was a transition in the trend line.
- Finally, the number of rows in the Max Spkies is counted.
20. Click on the card visual in the visualization pane.
21. Drag and drop Spikes (CM) from All Measures to the Fields.
Conclusion
Incorporating Power BI spike analysis and threshold-based analysis allows for a smart interpretation of the sales trends in comparison to the sales figures alone. The DAX measures allow us to detect not only those high-performing months but also the very instances at which there is a significant increase in sales.
Although High Months indicate the number of times that performance has surpassed expectations, Spikes reveal the turning points of the trend. This two-sided view can give companies greater knowledge about the changes in seasonal activity, demand, and performance of categories.
FAQs
The threshold is set to 75% of the maximum value of the monthly sales value with the help of the DAX measure.
High Months are those months in which sales have exceeded the threshold value.
Extended Learning & References
For more in-depth learning and advanced Power BI training, enroll in
- Microsoft 365 Copilot Training Courses – Practical Learning for Modern Work
- Power BI Advanced Training Course.
- Power BI Report Design
- Power BI Essentials
- Power BI DAX Essentials
- Power BI Service Course
Here, you can access detailed courses and resources designed to enhance your Power BI skills and take your reporting capabilities to the next level.
Downloads
How do you distinguish a genuine sales spike from a seasonal trend in Power BI? Would you recommend using DAX to calculate deviations from historical averages alongside the built-in anomaly detection feature for more reliable insights?