Forum Discussion

satkin's avatar
satkin
Icon for Advocate I rankAdvocate I
2 years ago
Solved

Standard Deviation as a column either in Power Query or DAX

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

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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's avatar
    parry2k
    2 years ago

    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 )
)

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    parry2k
    Icon for Super User rankSuper User
    2 years ago

    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 )
)