What is Power BI YOY% Measure?
Table of Contents
Introduction
The Year Over Year (YOY) % calculation plays a vital role in assessing the growth and performance of your business operations. Using YOY% you can easily compare the Current year Sales with Last year Sales, create Custom measures and visualize those results in Matrix visuals using Microsoft Power BI. In this blog you will learn how to create a Year Over Year (YOY) % measure using DAX Variables and Time Intelligence Functions in Power BI. Further, this tutorial will also help you get acquainted with Quick Measures feature in Power BI that calculates YOY % measures for you automatically without any need of complex knowledge about time intelligence.
To get further insight into queries and advanced calculation techniques in DAX, you may also refer to the following useful blog post about queries creation in DAX Studio:
Finding YOY %
To find YOY%:
- Right click on All Measures in the Fields.
- Click on New measure.
Measure tools open.
- Type the following formula in the formula bar and press Enter.
Custom YOY % (CM) =
VAR CurrentSales = [Total Sales Amount (CM)]
VAR PreviousSales = [Sales SPLY using SAMEPERODLASTYEAR (CM)]
VAR DeltaSales = CurrentSales – PreviousSales
VAR Result =
IF ( NOT ISBLANK ( CurrentSales ), DIVIDE ( DeltaSales, PreviousSales ) )
RETURN
Result
In this expression:
- The sum of the Sales Amount current filter context is found and stored in CurrentSales variable.
- The sales for the last year are stored in PreviousSales.
- The variable DeltaSales is used to find the difference between the values of the first two variables.
- Finally, at the end the DeltaSales is divided by the PreviousSales to get the change.
- Click on the “%” in the formatting section.
- Click on the matrix visual in the visualization pane.
- Drag and drop Year from the Date table to the rows.
Notice that automatic date hierarchy is created.
- Drag and drop Total Sales Amount (CM) and Custom YOY % (CM) from the All Measures table to the Values.
Quick Measures Option
With the Quick Measures option in Power BI, you can automatically generate the DAX for your complex measures.
8. Right click on All Measures in the Fields.
9. Click on New quick measure.
Pop-up menu appears.
10. From the Calculation menu, select year over year change.
11. Drag and drop Sales Amount from the Sales table to Base value.
12. Drag and drop Date from the Date table to Date selection box.
13. Type 1 in the Number of Periods.
Please note that “1” will be default option as well.
14. Click Add.
15. Drag and drop Sales Amount YoY% to the Values.
16. Click on Sales Amount YoY % in the All Measures table.
Sales Amount YoY% =
VAR __PREV_YEAR =
CALCULATE ( SUM ( ‘Sales'[Sales Amount] ), DATEADD ( ‘Date'[Date], -1, YEAR ) )
RETURN
DIVIDE ( SUM ( ‘Sales'[Sales Amount] ) – __PREV_YEAR, __PREV_YEAR )
The code for YoY% internally calls DATEADD().
Notice how descriptive variable names can make understanding the code much easier.
Conclusion
Through the process of understanding how to calculate YoY % in Microsoft Power BI, businesses will be able to make sound decisions based on historical performance patterns. The application of DAX variables, custom measures, and Quick Measures in the computation makes it easier for organizations to analyse their reports.
FAQs
YOY % is a metric used to compare a value, such as sales, between the current year and the previous year to measure growth or decline.
The advantage of using DAX to calculate YOY% is that you can perform dynamic calculations involving time intelligence functions easily.
A custom YOY measure is created manually using DAX expressions, whereas a quick measure automatically creates DAX expressions for you.
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
