Calculating STDEV of LN Returns

DAX DAX Calculations
1

Hello,

I am new to the community, the forum, and Dax and am feeling just a tad bit self-conscious as I write this question. I have attached my test file herein. I am attempting to compute the standard deviation of the natural log-returns of Close data from STOCKHISTORY in excel.

I am using Sam’s advice of attempting to work primarily with measures. Unfortunately, the STDEV function calls for a table reference. I cannot seem to figure out how to calculate this figure since my expression reference is a measure.

For now, I am just trying to pull a card visual into the model to check the calculation. My ultimate goal, for the moment, is to rebuild an excel model (see snapshot below).

snapshot1.5.22.pdf (178.1 KB)

Test Model_12.31.21.pbix (222.1 KB)

2

Welcome to the Enterprise DNA Forum @U810190. We’re happy to have you in the community. To get started, I highly suggest you check these guides that Sam created for us to build a more collaborative environment. How To Use The Enterprise DNA Support Forum

3

@U810190 ,

Welcome to the forum - great to have you here! And definitely no need to self-conscious about asking questions - we were all in exactly your same position at some point. Plus, your question is not at all trivial (but even if it were, that would be fine…).

So, basically what you need for the desired calculation is a table like this, that is dynamic based on the selections made in the slicers:

image
image558×774 73.1 KB

But that table does not physically exist in the data model, because as you correctly note LNReturn is a measure that is calculated in memory when needed. However, in DAX we can create tables virtually and use those tables in measures that call for a table parameter. So here, we can create the table above as a virtual table within a variable, and then use that virtual table within the STDEVX.S function:

Stand Dev of LNReturn = 

VAR vTable = 
CALCULATETABLE(
    ADDCOLUMNS(
        VALUES( ExtendedDates[Date] ),
        "@LnRet", [LNReturn]
    ),
    ALLSELECTED()
)

VAR Result =
STDEVX.S(
    vTable,
    [@LnRet]
)

RETURN Result

and here’s what it looks like all put together:

image
image2321×929 180 KB

I hope this is helpful. Full solution file attached below.

1 Like
4

Brian,

Thanks so much for the response. Your solution is very elegant and I am sure saved me countless hours of banging my head against the wall. I was just beginning to head down the CalculatedTable path but only just received my copy of “Definitive Guide to DAX” last week.

Also, I am just starting the Ultimate Beginners Guide to Dax in the learning center after completing the Beginners Guide to BI. I can’t say enough about how much more confident and excited I am about really becoming efficient with BI, in short order, thanks to this platform.

Thanks again,

Ken

1 Like