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 the period with the 'CALCULATE' function but clearly this isn't working (same for variance):

 

StDEV_1 = CALCULATE (STDEV.P(return_table[Return]), DATESINPERIOD(return_table[Date], LASTDATE(return_table[Date]),-1, year), NOT(ISBLANK(return_table[Return])))

 

Portfolio NameDateReturn (Month)
Growth_Portfolio_A10/31/20171.88%
Growth_Portfolio_A9/30/20171.29%
Growth_Portfolio_A8/31/20170.47%
Growth_Portfolio_A7/31/20171.87%
Growth_Portfolio_A6/30/20172.79%
Growth_Portfolio_A5/31/20174.23%
Growth_Portfolio_A4/30/20173.72%
Growth_Portfolio_A3/31/20170.90%
Growth_Portfolio_A2/28/20173.67%
Growth_Portfolio_A1/31/20177.98%
Growth_Portfolio_A12/31/2016-0.26%
Growth_Portfolio_A11/30/20161.40%
Growth_Portfolio_A10/31/2016-4.02%
Growth_Portfolio_A9/30/20161.82%
Growth_Portfolio_A8/31/20161.60%
Growth_Portfolio_A7/31/20168.62%
Growth_Portfolio_A6/30/2016-2.66%
Growth_Portfolio_A5/31/20161.34%
Growth_Portfolio_A4/30/20160.23%
Growth_Portfolio_A3/31/20167.15%
Growth_Portfolio_A2/29/2016-1.56%
Growth_Portfolio_A1/31/2016-14.42%
Growth_Portfolio_A12/31/2015-1.67%
Growth_Portfolio_A11/30/20151.89%
Growth_Portfolio_A10/31/20156.80%
Growth_Portfolio_A9/30/2015-5.02%
Growth_Portfolio_A8/31/2015-8.30%
Growth_Portfolio_A7/31/20154.28%
Growth_Portfolio_A6/30/20151.04%
Growth_Portfolio_A5/31/20152.70%
Growth_Portfolio_A4/30/20153.03%
Growth_Portfolio_A3/31/2015-1.63%
Growth_Portfolio_A2/28/20155.88%
Growth_Portfolio_A1/31/2015-1.29%
Growth_Portfolio_A12/31/2014-2.53%
Growth_Portfolio_A11/30/2014-1.14%
Growth_Portfolio_A10/31/20141.66%
Growth_Portfolio_A9/30/2014-4.40%

 

Thank you!

  • 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

3 Replies

  • 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