STDEV.S function in DAX to target specific cell range

[Anonymous]

Hi Everyone,

I badly need help to this, I've been searching for a solution in this community for days but still not successful.

I have a table in excel that I wanted to create as a calculated table using dax. I've already completed creating the date table, and other columns but I am having challenges to to use STDEV.S function to target on a specific cell range in my data table.

See the excel table and formula used in excel in the screenshot below.

Thanks in advance

Month Year	Column 1	Column 2	Std Dev	Total
Jul-16	0	0	0	0
Aug-16	0	0	0	0
Sep-16	0	0	0	0
Oct-16	0	0	0	0
Nov-16	0	0	0	0
Dec-16	0	0	0	0
Jan-17	3	1	1.511857892	4
Feb-17	4	2	2.375469878	6
Mar-17	5	3	3.16227766	8
Apr-17	6	4	3.91010088	10
May-17	7	5	4.631905165	12
Jun-17	8	6	5.334280219	14
Jul-17	9	7	6.021329609	16
Aug-17	10	8	6.71011483	18
Sep-17	11	9	7.29594197	20

Std Dev	Total
0	=C2+D2
=STDEV.S(F2:F3)	=C3+D3
=STDEV.S(F2:F4)	=C4+D4
=STDEV.S(F2:F5)	=C5+D5
=STDEV.S(F2:F6)	=C6+D6
=STDEV.S(F2:F7)	=C7+D7
=STDEV.S(F2:F8)	=C8+D8
=STDEV.S(F2:F9)	=C9+D9
=STDEV.S(F2:F10)	=C10+D10
=STDEV.S(F2:F11)	=C11+D11
=STDEV.S(F2:F12)	=C12+D12
=STDEV.S(F2:F13)	=C13+D13
=STDEV.S(F2:F14)	=C14+D14
=STDEV.S(F3:F15)	=C15+D15
=STDEV.S(F4:F16)	=C16+D16

[Anonymous]

Hi,

I badly needed help in this one as I haven't found any solution yet.

I have a calculated column and wanted to create a STDEV.S column using dax to calculate the first 13 cell then calculate 1 cell down (next 13) and so on.

If you'll look in an excel table, it is similar to below.

Index	Month Year	Column 1	Column 2	Std Dev	Total
1	Jul-16	0	0	0	0
2	Aug-16	0	0	0	0
3	Sep-16	0	0	0	0
4	Oct-16	0	0	0	0
5	Nov-16	0	0	0	0
6	Dec-16	0	0	0	0
7	Jan-17	3	1	1.511857892	4
8	Feb-17	4	2	2.375469878	6
9	Mar-17	5	3	3.16227766	8
10	Apr-17	6	4	3.91010088	10
11	May-17	7	5	4.631905165	12
12	Jun-17	8	6	5.334280219	14
13	Jul-17	9	7	6.021329609	16
14	Aug-17	10	8	6.71011483	18
15	Sep-17	11	9	7.29594197	20

Dev	Total
0	=C2+D2
=STDEV.S(F2:F3)	=C3+D3
=STDEV.S(F2:F4)	=C4+D4
=STDEV.S(F2:F5)	=C5+D5
=STDEV.S(F2:F6)	=C6+D6
=STDEV.S(F2:F7)	=C7+D7
=STDEV.S(F2:F8)	=C8+D8
=STDEV.S(F2:F9)	=C9+D9
=STDEV.S(F2:F10)	=C10+D10
=STDEV.S(F2:F11)	=C11+D11
=STDEV.S(F2:F12)	=C12+D12
=STDEV.S(F2:F13)	=C13+D13
=STDEV.S(F2:F14)	=C14+D14
=STDEV.S(F3:F15)	=C15+D15
=STDEV.S(F4:F16)	=C16+D16

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

Please try to use below calculated column formulas if they suitable for your requirement:

Total = [Column 1]+[Column 2]

STDEV.S =
VAR curr = [Month Year]
RETURN
    STDEVX.S (
        FILTER (
            ALL ( T5 ),
            [Month Year]
                >= DATE ( YEAR ( curr ) - 1, MONTH ( curr ), DAY ( curr ) )
                && [Month Year] <= curr
        ),
        [Total]
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft ,

I was able to replicate the formula you've provided and make it works. This is what I am looking for.

Thanks you very much for your help on this.

[v-shex-msft]

Hi @Anonymous ,

AFAIK, power bi data model not contains row index and column index.
I'd like to suggest you add a index column on query editor side, then you can write complex conditions to return correspond calculation formula based on current row index.

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for the info @v-shex-msft .

Already created an index using the formula below as my table is created using dax:

Index = CALCULATE(COUNTROWS('CR Trend'), FILTER(ALL('CR Trend'), 'CR Trend'[Year-MonthName].[Date]<= EARLIER('CR Trend'[Year-MonthName].[Date])))

However tried creating some conditions but it's not working. I don't know what functions to be use etc as I am new to DAX functions.

Any suggestion how to build that condition is very much appreaciated.