STDEV and VAR for Date Period

[vencenz]

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 Name	Date	Return (Month)
Growth_Portfolio_A	10/31/2017	1.88%
Growth_Portfolio_A	9/30/2017	1.29%
Growth_Portfolio_A	8/31/2017	0.47%
Growth_Portfolio_A	7/31/2017	1.87%
Growth_Portfolio_A	6/30/2017	2.79%
Growth_Portfolio_A	5/31/2017	4.23%
Growth_Portfolio_A	4/30/2017	3.72%
Growth_Portfolio_A	3/31/2017	0.90%
Growth_Portfolio_A	2/28/2017	3.67%
Growth_Portfolio_A	1/31/2017	7.98%
Growth_Portfolio_A	12/31/2016	-0.26%
Growth_Portfolio_A	11/30/2016	1.40%
Growth_Portfolio_A	10/31/2016	-4.02%
Growth_Portfolio_A	9/30/2016	1.82%
Growth_Portfolio_A	8/31/2016	1.60%
Growth_Portfolio_A	7/31/2016	8.62%
Growth_Portfolio_A	6/30/2016	-2.66%
Growth_Portfolio_A	5/31/2016	1.34%
Growth_Portfolio_A	4/30/2016	0.23%
Growth_Portfolio_A	3/31/2016	7.15%
Growth_Portfolio_A	2/29/2016	-1.56%
Growth_Portfolio_A	1/31/2016	-14.42%
Growth_Portfolio_A	12/31/2015	-1.67%
Growth_Portfolio_A	11/30/2015	1.89%
Growth_Portfolio_A	10/31/2015	6.80%
Growth_Portfolio_A	9/30/2015	-5.02%
Growth_Portfolio_A	8/31/2015	-8.30%
Growth_Portfolio_A	7/31/2015	4.28%
Growth_Portfolio_A	6/30/2015	1.04%
Growth_Portfolio_A	5/31/2015	2.70%
Growth_Portfolio_A	4/30/2015	3.03%
Growth_Portfolio_A	3/31/2015	-1.63%
Growth_Portfolio_A	2/28/2015	5.88%
Growth_Portfolio_A	1/31/2015	-1.29%
Growth_Portfolio_A	12/31/2014	-2.53%
Growth_Portfolio_A	11/30/2014	-1.14%
Growth_Portfolio_A	10/31/2014	1.66%
Growth_Portfolio_A	9/30/2014	-4.40%

Thank you!

[Greg_Deckler]

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]

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 · Accepted solution]

Hi @vencenz,

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

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