St. Deviation of Portfolio Returns

[badger007]

Hi everyone,

I have a table with some portfolios and their daily values. Negative values mean they are short some assets.

My ultimate goal is to get a standard deviation of the combined daily returns of selected portfolios. So if I select Portfolio 1 and Portfolio 2, I want a st. deviation of the combined returns of the two.

I only managed to get a st. deviation of the Value column. But it's not what I need. I'm struggling to find a solution to my problem.

The returns = (ValueToday - ValueYesterday) / ValueYesterday

The pbix file is saved here

Will appreciate your help.
Thanks very much.

[lbendlin]

In your measure get a list of all dates in the current filter context, then compute the return for each, and finally run the standard deviation function over that table.

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[badger007]

Hi lbendlin,

Thanks for your reply. The pbix file is saved here.

It'll be so great if you can help me with this.

Thanks!

[lbendlin · Accepted solution]

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

Small comment: your "Day over Day"  measure includes transition from negative to positive values. In that scenario it is advisable to adjust the formula to use the absolute of the prior value.

m_Daily_Returns = 
VAR thisvalue = 
    MAX ( 'Table'[value] )
VAR thisdate =
    MAX ( 'Table'[Date] )
VAR prevvalue =
    CALCULATE (
        LASTNONBLANKVALUE (
            'Table'[Date],
            MAX ( 'table'[value] )
        ),
        FILTER (
            ALL ( 'Table'[Date] ),
            'table'[Date] < thisdate
        )
    )
RETURN
    DIVIDE (
        (thisvalue - prevvalue),
        abs(prevvalue)
    ) 

Next you need to rethink your entire approach.  For example 

You have gaps in your dates (weekends, I assume) that you will need to take into account (or not - but you need to decide). You can also see that the row totals are incorrect. They need to be calculated separately for each portfolio based not on the sum, not on the maximum, but on the last value.

Pval = 
var a = values(Portfolios[Portfolio])
var b = ADDCOLUMNS(a,"md",var p=[Portfolio] return CALCULATE(max('Table'[Date]),'Table'[Portfolio]=p))
var c = ADDCOLUMNS(b,"mv",var md=[md] var p =[Portfolio] return CALCULATE(sum('Table'[Value]),'Table'[Date]=md,'Table'[Portfolio]=p))
return sumx(c,[mv])

The same is then true for the daily returns and the standard deviation. See attached.

[badger007]

Thanks very much for your help!

This solved my problem.