Measure to Get Standard Deviation of Measure

[PowThomas]

Hello,

I have a measure with the following DAX:

Logs_7d moving average =

VAR period = 7

VAR the_first_date =

CALCULATE ( FIRSTDATE ( Logs[Date] ), ALLSELECTED ( Logs[Date] ) )

VAR last_date =

LASTDATE ( Logs[Date] )

VAR sum_in_period =

CALCULATE (

SUM ( Logs[Value] ),

DATESINPERIOD ( Logs[Date], last_date, - period, DAY )

)

RETURN

IF ( last_date - the_first_date >= period - 1, sum_in_period / period )

And I would like to now calculate 2* the standard deviation of that measure in order to create upper and lower bollinger bands. I have attempted the solutions in the following links with no success:

Solved: Standard Deviation of Calculated Measure Not Worki... - Microsoft Power BI Community

Solved: Blank Rows and Standard Deviation for a Calculated... - Microsoft Power BI Community

Here is a link to the file: https://1drv.ms/u/s!ArHGjxjyPrTJm04qDxjTjv-jp9E1

Edit: 

In case anyone wants to create bollinger bands in the future for a daily chart a la the below pic:

Blue line is the actual price line, orange is a 20 -day simple moving average, purple are the top and bottom bollinger bands.The following measure is for the SMA:

The standard deviations can be calculated using the same formula, but for the sum_in_period variable replace sum() with stdv.p() and remove the last division by the period like below:

Finally, you can just follow the equation for calculation the bands from here. 

Here is the upper for instance:

And the lower:

[AlexisOlson · Accepted solution]

The pattern that should apply is this:

Logs_7d_MA_StdDev = 
STDEVX.P (
    VALUES ( DateTable[Date] ),
    [Logs_7d moving average]
)

However, this won't work as-is because your 7-day moving average is written using Logs[Date] instead of DateTable[Date]. If you update that measure to replace each of the former with the latter, then your Std Dev measure should be good to go.

[PowThomas]

Okay, I tried that but my sma and new standard deviation measure both do not chart. Maybe something wrong with my date table? Any ideas?

Standard Deviation MeasureSMAChart

[AlexisOlson]

Use DateTable[Date] for the chart axis and update the 14-day measure too.

[PowThomas]

I did both of the recommendations you said. I can tell it did calculate something because when I put the measure on a tile, it gives me back the values and they change over time with a date slider. Unfortunately, I still can't get the measure to chart. 

[AlexisOlson]

I can't tell what the problem is but I'm attaching the copy I modified where it does appear to be working.

[PowThomas]

I appreciate your help. I'd been able to overlay the different SMAs. I was trying to implement bollinger bands. I did end up getting it by just substituting stdv.p to my SMA measure where the average calc went and taking off the division by period at the end. I'm going to add pics of the calcs and the result to my op in case it can help others solve their problems.

[AlexisOlson]

The basic approach in the first link should work but might need a bit of adjustment to your particular case.

What granularity are you trying to compute the StdDev over? Daily?

I couldn't get the link to your file to work.

[PowThomas]

Yes, it's daily over 180 days.

I fixed the link in the post. Helps if I post a shareable link lol.