I have a table containing the monthly returns of a portfolio, its benchmark and the monthly excess (portfolio - benchmark).
Table 1 extract
| Date | Attribute | Value |
|----------------|-----------|-------|
| 30 June 2025 | Portfolio | 0.05 |
| 30 June 2025 | Benchmark | 0.03 |
| 30 June 2025 | Excess | 0.02 |
| 31 May 2025 | Portfolio | -0.02 |
| 31 May 2025 | Benchmark | -0.01 |
| 31 May 2025 | Excess | -0.01 |
| 30 April 2025 | Portfolio | 0.01 |
| 30 April 2025 | Benchmark | 0.01 |
| 30 April 2025 | Excess | 0.00 |
I want to visualise the cumulative 3 month portfolio, benchmark and excess return in a matrix as follows, with the option to toggle between end dates.
| Type | 3M |
|-----------|-------|
| Portfolio | 3.93% |
| Benchmark | 2.99% |
| Excess | 0.94% |
Using the below DAX measure I'm able to get the Portfolio and Benchmark returns to display correctly:
M.3M =
VAR EndDate = MAX(table1[Date])
VAR StartDate = EDATE(EndDate, -2)
VAR Portfolio =
CALCULATE(
PRODUCTX(table1, 1+table1[Value])-1,
table1[Date] >= StartDate && table1[Date] <= EndDate && table1[Attribute] = "Portfolio", REMOVEFILTERS(table1[Date])
)
VAR Benchmark =
CALCULATE(
PRODUCTX(table1, 1+table1[Value])-1,
table1[Date] >= StartDate && table1[Date] <= EndDate && table1[Attribute] = "Benchmark", REMOVEFILTERS(table1[Date])
)
VAR ExcessReturn = Portfolio - Benchmark
RETURN
SWITCH(
TRUE(),
SELECTEDVALUE(table1[Attribute]) = "Portfolio", Portfolio,
SELECTEDVALUE(table1[Attribute]) = "Benchmark", Benchmark,
SELECTEDVALUE(table1[Attribute]) = "Excess", ExcessReturn,
BLANK()
)
However the resulting matrix is showing 0% as the excess return due to the filter context in the DAX measure:
| Type | 3M |
|-----------|-------|
| Portfolio | 3.93% |
| Benchmark | 2.99% |
| Excess | 0.00% |
How can I get the excess to display properly (note that I can't simply compound the monthly excess to get the 3 month excess)?
Appreciate any assistance.
You need to ignore the current filter on Attribute when calculating the excess return. This might not be the most efficient way of doing it, but it seems to work:
M.3M =
VAR EndDate = MAX(table1[Date])
VAR StartDate = EDATE(EndDate, -2)
VAR PortfolioReturn =
CALCULATE(
PRODUCTX(FILTER(table1, table1[Attribute] = "Portfolio" && table1[Date] >= StartDate && table1[Date] <= EndDate), 1 + table1[Value]) - 1,
REMOVEFILTERS(table1[Attribute])
)
VAR BenchmarkReturn =
CALCULATE(
PRODUCTX(FILTER(table1, table1[Attribute] = "Benchmark" && table1[Date] >= StartDate && table1[Date] <= EndDate), 1 + table1[Value]) - 1,
REMOVEFILTERS(table1[Attribute])
)
VAR ExcessReturn = PortfolioReturn - BenchmarkReturn
RETURN
SWITCH(
SELECTEDVALUE(table1[Attribute]),
"Portfolio", PortfolioReturn,
"Benchmark", BenchmarkReturn,
"Excess", ExcessReturn,
BLANK()
)