Hi @manojk_pbi ,
You can achieve this result by creating a series of DAX measures and then arranging them in a Matrix visual using a helper table.
First, it is essential that you have a proper date column in your model. Time intelligence functions like DATEADD, which you'll need for the "Previous Month" calculation, require a valid date data type to work correctly. Text values like "25-Jun" should be converted into a date, for example 6/1/2025, which can be done easily in Power Query.
Once your dates are set up, you should create base measures for your core calculations. This is a good practice that makes your DAX model easier to read and manage. Create one measure for the weighted average OTR and another for the average Delay. Remember to replace 'YourTable' with the actual name of your data table.
OTR_Base =
DIVIDE(
SUMX('YourTable', 'YourTable'[OTR] * 'YourTable'[CPD]),
SUM('YourTable'[CPD])
)
Delay_Base =
AVERAGE('YourTable'[Delay])
With these base measures, you can now create the specific measures for the Current Month (CM), Previous Month (PM), and the difference. The PM calculation uses the CALCULATE function to modify the filter context, telling Power BI to perform the calculation on the date range shifted back by one month using DATEADD. The difference is a simple subtraction.
// --- OTR Measures ---
OTR KPI CM = [OTR_Base]
OTR KPI PM =
CALCULATE(
[OTR_Base],
DATEADD('YourTable'[MONTHYEAR], -1, MONTH)
)
OTR Diff = [OTR KPI CM] - [OTR KPI PM]
// --- Delay Measures ---
Delay KPI CM = [Delay_Base]
Delay KPI PM =
CALCULATE(
[Delay_Base],
DATEADD('YourTable'[MONTHYEAR], -1, MONTH)
)
Delay Diff = [Delay KPI CM] - [Delay KPI PM]
After creating these, be sure to format them correctly from the Measure tools ribbon—set the OTR measures to Percentage and the Delay measures to Whole Number. To get the exact visual layout, you will use a Matrix. This requires creating a small, disconnected table to define the row structure. Use the Enter Data feature on the Home ribbon to create a table (you can call it KPI Structure) with columns named Category, Metric, and SortOrder to list out each row you want to see ("OTR", "KPI CM", etc.).
With that helper table created, you need one final measure that acts as a "switcher." This measure will check which row of the matrix is being calculated and show the corresponding measure you already created. This pattern is very powerful for creating custom financial or KPI reports.
KPI Value =
VAR SelectedCategory = SELECTEDVALUE('KPI Structure'[Category])
VAR SelectedMetric = SELECTEDVALUE('KPI Structure'[Metric])
RETURN
SWITCH(
TRUE(),
SelectedCategory = "OTR" && SelectedMetric = "KPI CM", [OTR KPI CM],
SelectedCategory = "OTR" && SelectedMetric = "KPI PM", [OTR KPI PM],
SelectedCategory = "OTR" && SelectedMetric = "Diff", [OTR Diff],
SelectedCategory = "Delay" && SelectedMetric = "KPI CM", [Delay KPI CM],
SelectedCategory = "Delay" && SelectedMetric = "KPI PM", [Delay KPI PM],
SelectedCategory = "Delay" && SelectedMetric = "Diff", [Delay Diff],
BLANK()
)
To finish, place a Matrix visual on your report. Drag the Category and Metric columns from your KPI Structure table to the Rows field, and drag your new [KPI Value] measure to the Values field. Add a slicer for your date column, and your visual will now dynamically show the current and previous month values based on the selection.
Best regards,
