Standard Deviation

[Uzi2019]

Hi Expert,

Could you please help me to compute STDEV of revenue in DAX? I am attaching below data along with how it's working in exce

date	source	channel	revenue	Expected Output SD	SD Formula
1/1/2022	A	ABC	100
1/2/2022	A	ABC	103	2.121320344	=STDEV.S(D2:D3)
1/3/2022	A	ABC	108	3.535533906	=STDEV.S(D3:D4)
1/4/2022	A	ABC	928	579.8275606	=STDEV.S(D4:D5)
1/5/2022	A	ABC	283	456.0838739	=STDEV.S(D5:D6)
1/1/2022	A	ABC	100
1/2/2022	A	ABC	200	70.71067812	=STDEV.S(D7:D8)
1/3/2022	A	ABC	300	70.71067812	=STDEV.S(D8:D9)
1/4/2022	A	ABC	202	69.29646456	=STDEV.S(D9:D10)
1/5/2022	A	ABC	205	2.121320344	=STDEV.S(D10:D11)
1/1/2022	B	XYZ	100
1/2/2022	B	XYZ	300	141.4213562	=STDEV.S(D12:D13)
1/3/2022	B	XYZ	303	2.121320344	=STDEV.S(D13:D14)
1/4/2022	B	XYZ	305	1.414213562	=STDEV.S(D14:D15)
1/5/2022	B	XYZ	308	2.121320344	=STDEV.S(D15:D16)
1/1/2022	B	XYZ	100
1/2/2022	B	XYZ	400	212.1320344	=STDEV.S(D17:D18)
1/3/2022	B	XYZ	402	1.414213562	=STDEV.S(D18:D19)
1/4/2022	B	XYZ	407	3.535533906	=STDEV.S(D19:D20)
1/5/2022	A	XYZ	411	2.828427125	=STDEV.S(D20:D21)

[v-jianboli-msft · Accepted solution]

Hi @Uzi2019 ,

Please try:

First create an index column:

Then apply the measure:

Measure =
VAR _a =
    STDEVX.S (
        FILTER (
            ALL ( 'Table' ),
            [Index] <= MAX ( 'Table'[Index] )
                && [Index]
                    >= MAX ( 'Table'[Index] ) - 1
        ),
        [revenue]
    )
VAR _b =
    MINX ( ALLEXCEPT ( 'Table', 'Table'[channel] ), [date] )
RETURN
    IF ( MAX ( 'Table'[date] ) = _b, BLANK (), _a )

Then show items with no data:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Uzi2019]

Thanks alot. It worked for me.

[Greg_Deckler]

@Uzi2019 Try something like this:

Measure = 
  VAR __Date = MAX('Table'[Date])
  VAR __NextDate = MINX(FILTER(ALL('Table),[Date] > __Date),[Date])
  VAR __CurrentValue = MAX('Table'[revenue])
  VAR __NextValue = MAXX(FILTER(ALL('Table'),[Date] = __NextDate),[revenue])
  VAR __Result = STDDEVX.S( { __CurrentValue, __NextValue }, [Value])
RETURN
  __Result

[Uzi2019]

@Greg_Deckler Thanks for your reply. I tried your solution but not getting expected output like I mentioned in my first post.