Financial DX / Power BI Dashboard Creation Series
Introduction
In Vol.5, we covered the basics of DAX and the concept of Measures, and created four basic Measures: Operating Margin, Defect Rate, Inventory Turnover, and Utilization Rate.
In this article, we will create the DAX Measures needed to display Year-over-Year (YoY) performance and Target Achievement in Card Visuals, as preparation for building the dashboard visuals in the upcoming articles.
The DAX created in this article is based on the four basic Measures created in Vol.5.
Step 1: Calculate Year-over-Year (YoY) Performance
First, we will create a DAX Measure that compares the KPI value for the selected period with the KPI value for the same period in the previous year.
Power BI provides Time Intelligence functions for time-series analysis, such as year-over-year or month-over-month comparisons. In this article, we will use the commonly used SAMEPERIODLASTYEAR function to perform a year-over-year comparison.
In Vol. 4, we created a Calendar table and configured it as a Date table. One reason for doing this is to enable this type of time-series analysis.
First, let’s calculate the year-over-year change in Operating Margin. Enter the following DAX formula in the Fact_monthly_kpi table to create the YoY Measure.
Operating Margin YoY =
VAR CurrentValue = [Operating Margin Measure]
VAR PriorYearValue =
CALCULATE(
[Operating Margin Measure],
SAMEPERIODLASTYEAR(Calendar[Date])
)
RETURN
DIVIDE(
CurrentValue - PriorYearValue,
PriorYearValue
)
This DAX formula defines two variables using VAR.
The first is the CurrentValue variable. It retrieves the value for the currently selected period from the Operating Margin Measure created in Vol. 5.
The second is the PriorYearValue variable. By combining CALCULATE and SAMEPERIODLASTYEAR, it retrieves the Operating Margin Measure value for the same period in the previous year.
The RETURN statement then returns the calculation result using these two variables.
Finally, DIVIDE calculates the YoY percentage change by dividing the difference between the current value and the prior-year value by the prior-year value.
Using the same approach, create YoY Measures for Utilization Rate, Inventory Turnover, and Defect Rate.
Utilization Rate YoY =
VAR CurrentValue = [Utilization Rate Measure]
VAR PriorYearValue =
CALCULATE(
[Utilization Rate Measure],
SAMEPERIODLASTYEAR(Calendar[Date])
)
RETURN
DIVIDE(
CurrentValue - PriorYearValue,
PriorYearValue
)Inventory Turnover YoY =
VAR CurrentValue = [Inventory Turnover Measure]
VAR PriorYearValue =
CALCULATE(
[Inventory Turnover Measure],
SAMEPERIODLASTYEAR(Calendar[Date])
)
RETURN
DIVIDE(
CurrentValue - PriorYearValue,
PriorYearValue
)Defect Rate YoY =
VAR CurrentValue = [Defect Rate Measure]
VAR PriorYearValue =
CALCULATE(
[Defect Rate Measure],
SAMEPERIODLASTYEAR(Calendar[Date])
)
RETURN
DIVIDE(
CurrentValue - PriorYearValue,
PriorYearValue
)Step 2: Convert the YoY Result into Readable Text
The YoY Measures created in Step 1 return numeric values. For example, a result of 0.125 means a 12.5% increase.
To make the result easier to read in a Card Visual, we will create a YoY Text Measure that calls the YoY Measure created in Step 1 and uses the FORMAT function to convert the result into percentage text.
For Operating Margin, use the following DAX formula to create the YoY Text Measure.
The FORMAT function converts the YoY result into a percentage with one decimal place. It also displays a “+” sign for positive changes and a “-” sign for negative changes.
In addition, the IF function is used to display No prior year data when the YoY calculation returns BLANK.
Operating Margin YoY Text =
VAR Diff = [Operating Margin YoY]
RETURN
IF(
ISBLANK(Diff),
"No prior year data",
"YoY " & FORMAT(Diff, "+0.0%;-0.0%")
)
Using the same approach, create Text Measures for the remaining three KPIs.
Utilization Rate YoY Text =
VAR Diff = [Utilization Rate YoY]
RETURN
IF(
ISBLANK(Diff),
"No prior year data",
"YoY " & FORMAT(Diff, "+0.0%;-0.0%")
)Inventory Turnover YoY Text =
VAR Diff = [Inventory Turnover YoY]
RETURN
IF(
ISBLANK(Diff),
"No prior year data",
"YoY " & FORMAT(Diff, "+0.0%;-0.0%")
)Defect Rate YoY Text =
VAR Diff = [Defect Rate YoY]
RETURN
IF(
ISBLANK(Diff),
"No prior year data",
"YoY " & FORMAT(Diff, "+0.0%;-0.0%")
)Step 3: Assess Target Achievement
Next, we will create Measures to determine whether each KPI has achieved its target.
Target values often change depending on business conditions, so it is generally recommended to store them as data. However, when target values do not change frequently, they can also be defined directly in DAX.
In this article, we will demonstrate how to define target values directly in DAX. Therefore, the target values for Operating Margin and Defect Rate are not included in the Sample CSV File provided in Vol. 3.
To keep the example simple, we will define the following targets:
Target of Operating Margin: 15%
Target of Defect Rate: 2%
For each KPI, use the IF function to compare the actual value with the target value and determine whether the target has been achieved.
In the Card Visual, we will display both the target value and the achievement status. When the target is achieved, the card will display “✓ Achieved”; otherwise, it will display “Not Achieved”.
Note that Defect Rate is a lower-is-better KPI. Therefore, unlike the other KPIs, the target is considered achieved when the actual value is less than or equal to the target.
Operating Margin Target Text =
IF(
[Operating Margin Measure] >= 0.15,
"Target: 15%; ✓ Achieved",
"Target: 15%; Not Achieved"
)Defect Rate Target Text =
IF(
[Defect Rate Measure] <= 0.02,
"Target: 2%; ✓ Achieved",
"Target: 2%; Not Achieved"
)
For Utilization Rate and Inventory Turnover, the Sample CSV File includes columns containing the target values.
Therefore, we will retrieve the target value from these Target columns, compare it with the actual KPI value, and determine whether the target has been achieved using DAX.
To keep the DAX simple, we use AVERAGE() to retrieve the Target value. In a real-world implementation, use the appropriate method based on how your target data is structured.
Utilization Rate Target Text =
VAR TargetValue =
AVERAGE(Fact_equipment_utilization[Target Utilization Rate])
RETURN
IF(
[Utilization Rate Measure] >= TargetValue,
"Target: " & FORMAT(TargetValue, "0.0%") & "; ✓ Achieved",
"Target: " & FORMAT(TargetValue, "0.0%") & "; Not Achieved"
)Inventory Turnover Target Text =
VAR TargetValue =
AVERAGE(Fact_inventory_turnover[Target Inventory Turnover])
RETURN
IF(
[Inventory Turnover Measure] >= TargetValue,
"Target: " & FORMAT(TargetValue, "0.0") & "; ✓ Achieved",
"Target: " & FORMAT(TargetValue, "0.0") & "; Not Achieved"
)
Step 4: Save the file
Once you have finished creating the Measures, save the file using Ctrl + S.
In addition to the four basic Measures created in Vol.5, you should now have 12 additional Measures:
- 4 YoY Measures
- 4 YoY Text Measures
- 4 Target Text Measures
In the next article, Vol.7: Slicers and Filters, we will place the Measures created in this article into actual Card Visuals and build the dashboard by combining them with period and Line Slicers and various charts.
Frequently Asked Questions
1. What is Time Intelligence?
Time Intelligence in Power BI refers to functionality used to perform time-series analysis based on dates, such as year-over-year comparisons, month-over-month comparisons, and year-to-date calculations.
In the YoY Measures created in this article, SAMEPERIODLASTYEAR is used to retrieve the corresponding period from the previous year.
To use Time Intelligence effectively, it is important to create an appropriate Calendar Table and configure it as a Date table.
In this series, we use the Calendar Table created in Vol.4.
2. When should SAMEPERIODLASTYEAR be used?
SAMEPERIODLASTYEAR is used when you want to retrieve the same period in the previous year based on the currently selected period.
For example, if you select January through June 2026, the function can retrieve January through June 2025 for comparison.
In this dashboard, we use it to compare the KPI for the selected period with the KPI for the same period in the previous year.
Functions such as DATEADD can also be used to shift a date period. However, when the specific purpose is to compare the same period with the previous year, SAMEPERIODLASTYEAR provides a simple and straightforward approach.
3. How is VAR used, and what are its benefits?
VAR is used to assign a name to an intermediate calculation result and temporarily store that value.
The main benefits of using VAR are:
・It makes DAX easier to read.
・It avoids repeating the same calculation multiple times.
・It allows complex calculations to be broken down into logical steps.
As DAX becomes more complex, using VAR to break calculations into smaller parts becomes increasingly useful.
4. What is the FORMAT function used for?
The FORMAT function converts a numeric value into text using a specified format.
In this article, we use FORMAT to make YoY results easier to read by specifying the number of decimal places and displaying the result as a percentage.
5. When should Card Visuals be used?
In a management dashboard, Card Visuals are typically used to display important KPI values that allow users to quickly understand the current business situation, such as Revenue, Operating Margin, and Year-over-Year performance.
On the other hand, when you want to examine monthly trends or long-term changes compared with the previous year, charts such as a Line Chart are generally more appropriate.
In this dashboard, the Card Visuals display the current KPI value, YoY performance, and target achievement status. The objective is to allow users to understand the current business performance quickly.
When the selected period changes, the values displayed in the Cards also change accordingly. This allows users to examine KPIs for a specific year, quarter, or month.
Summary
In this article, we added Year-over-Year (YoY) and Target Achievement calculations based on the four basic Measures created in Vol.5.
| Added DAX Metrics | Key DAX Functions Used |
|---|---|
| YoY | SAMEPERIODLASTYEAR、CALCULATE、DIVIDE |
| YoY Text | IF、FORMAT |
| Target Text | IF、AVERAGE |
With that, we now have all the key DAX formulas needed to display the data on the card visuals.
Next Episode Preview
In the next installment, Vol. 7, we’ll place the Measures we created this time into an actual visualization and build a dashboard by combining them with slicers and charts.