Forum Discussion

vencenz's avatar
vencenz
Regular Visitor
8 years ago
Solved

STDEV and VAR for Date Period

Hoping a kind soul could help with formula for calculating StDev and Variance of portfolio returns for a given trailing period (ie last 12-months or 3-years). I've tried using STDEV.P and defining th...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi vencenz,

     

    Based on my test, the formula below should work in your scenario. :smileyhappy:

    StDEV_1 =
VAR year =
    YEAR ( MAX ( return_table[Date] ) )
RETURN
    CALCULATE (
        STDEV.P ( return_table[Return (Month)] ),
        FILTER (
            ALL ( return_table ),
            YEAR ( return_table[Date] )
                >= year - 1
                && NOT ( ISBLANK ( return_table[Return (Month)] ) )
        )
    )

    Regards

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

Can you not just create a measure:

 

MyStdDev = STDEV.P([Return])

And then just use a Date slicer?

 

Or, if you really want to hard code it to TOTALYTD, use that function:

 

YtdStdDev = TOTALYTD(STDEV.P([Return]),Portfolio[Date])
vencenz's avatar
vencenz
Regular Visitor
8 years ago
Thanks for the assistance! I need to be able to hard code because the display page is planned to show statistics based on returns for the trailing 1-, 3- and 5-year periods. Just can’t figure how to hard code the periods. Sorry if this is dumb question and I’m overlooking a simple solution
  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi vencenz,

     

    Based on my test, the formula below should work in your scenario. :smileyhappy:

    StDEV_1 =
VAR year =
    YEAR ( MAX ( return_table[Date] ) )
RETURN
    CALCULATE (
        STDEV.P ( return_table[Return (Month)] ),
        FILTER (
            ALL ( return_table ),
            YEAR ( return_table[Date] )
                >= year - 1
                && NOT ( ISBLANK ( return_table[Return (Month)] ) )
        )
    )

    Regards