Forum Discussion
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...
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
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?
Hi DataDiva,
According to the error message, it sound like your columns not from in same table, right?
If this is a case, earlier function not works for this scenario.Can you please share some sample data or pbix file for test? It will help for clarify table structure and coding formula.
Regards,
Xiaoxin Sheng