powerbidaxvisualizationpowerbi-desktopmeasure

Power BI: DAX measure to calculate portfolio, benchmark and excess returns in a matrix filter context


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.


Solution

  • 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()
        )
    

    enter image description here