Standard Deviation as a column either in Power Query or DAX

[satkin]

Hi,

Below is a screen shot of a standard deviation calculation per variable for the prior 6 months observations.  Column E shows the Excel formula used in column D.

My raw data is columns A-C, and I need to replicate the creation of column D in Power BI, either through Power Query or DAX.  I've made numerous attempts, but am clearly missing something as I just don't seem able to do it.  Is this possible?  If so any guidance on how would be appreciated.

Many thanks

[rajendraongole1 · Accepted solution]

Hi @satkin - I am assuming your table is named standardObservations and your columns are date, Variables, and Values

create a dax measure as follows and before that I hope you have a seperate date table created in your model. if not please create it.

Measure:

StdDevLast6Months =
VAR CurrentDate = MAX('standardObservations'[Date])
VAR StartDate = EDATE(CurrentDate, -6)
RETURN
CALCULATE(
STDEV.P('standardObservations'[Values]),
FILTER(
'standardObservations',
'standardObservations'[Date] >= StartDate &&
'standardObservations'[Date] <= CurrentDate &&
'standardObservations'[Variable] = MAX('Observations'[Variable])
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[parry2k · Accepted solution]

@satkin although @rajendraongole1  has given the solution, as a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

and then I will write a DAX measure like this:

StdDevLast6Months =
CALCULATE(
STDEV.P('standardObservations'[Values]),
DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -6, MONTH )
)

[rajendraongole1 · Accepted solution]

Hi @satkin - I am assuming your table is named standardObservations and your columns are date, Variables, and Values

create a dax measure as follows and before that I hope you have a seperate date table created in your model. if not please create it.

Measure:

StdDevLast6Months =
VAR CurrentDate = MAX('standardObservations'[Date])
VAR StartDate = EDATE(CurrentDate, -6)
RETURN
CALCULATE(
STDEV.P('standardObservations'[Values]),
FILTER(
'standardObservations',
'standardObservations'[Date] >= StartDate &&
'standardObservations'[Date] <= CurrentDate &&
'standardObservations'[Variable] = MAX('Observations'[Variable])
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!