Forum Discussion

DataDiva's avatar
DataDiva
Advocate II
8 years ago

dynamic standard deviation

Hi! Someone may have provided a solution to this problem somewhere, but I haven't found it yet...    I need to create a multi-layered dynamic standard deviation calculation that helps my users iden...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

HI DataDiva,

 

I think you can refer to below formula if it suitable for your requirement.

 

Sample:

1. Calculation rolling 12 month standard deviation.

 

Rolling 12 Month SD LineValue =
CALCULATE (
    STDEV.P ( Table[LineValue] ),
    FILTER (
        ALL ( Table ),
        Table[Date]
            >= DATE ( YEAR ( [Date] ), MONTH ( [Date] ) - 12, DAY ( [Date] ) )
            && Table[Date] < EARLIER ( [Date] )
            && Table[Hospital ID] = EARLIER ( Table[Hospital ID] )
            && Table[LineItem] = EARLIER ( Table[LineItem] )
    )
)

 

2. Loop through above column to tag unusually records.

Tag =
VAR defineValue = 1
VAR result =
    CALCULATE (
        STDEV.P ( Table[Rolling 12 Month SD LineValue] ),
        FILTER (
            ALL ( Table ),
            Table[Date]
                >= [Date] - 3
                && Table[Date] < EARLIER ( [Date] )
                && Table[Hospital ID] = EARLIER ( Table[Hospital ID] )
                && Table[LineItem] = EARLIER ( Table[LineItem] )
        )
    )
RETURN
    IF ( result > defineValue, "Y", "N" )

 

Regards,

Xiaoxin Sheng

DataDiva's avatar
DataDiva
Advocate II
8 years ago

Thank you for your suggestion. However, I tried the first calculation and got, "Ealier/earlist refers to an ealier row context which doesn't exist." 

 

Can you explain how this calculation is supposed to work?