Forum Discussion

Ortignano's avatar
Ortignano
Icon for Helper II rankHelper II
5 years ago
Solved

last 12 months standard deviation on a table

Hello,

I have a table (product) of product category with Quantity,year,month and monthID inside it (monthID is equal to (year-2006)*12+monthnumber).

For example

Product Qty Year  month

A           5      2020 1

B          2       2020 3

I would calculate the standard deviation of the product category on last 12 months (some products can have qty 0 in certian month)

 

So I create a measure Total_Case_YTD:=SUM(Product[Quantity])  and 

 

STDEV Qty over Last 12 Months:=STDEVX.P (
FILTER (
Product;
Product[MonthID] <= MAX ( Product[MonthID])
&& Product[MonthID]
>= MAX (Product[MonthID] ) - 11
);
 Total_Case_YTD+0
)

 

But when I pivot them (row with product, column standard deviation) all standard deviation are equal to zero

 

I.e

Product STD deviation

A            0

B            0

 

Do you have any suggestion?

 

Thanks

Antonio

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Ortignano 

     

    Based on your descirption, I created data to reproduce your scenario. The pbix file is attached in the end.

    Tab:

     

    Calendar(a calculated table):

    Calendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "YM",
    YEAR([Date])*100+MONTH([Date])
)

     

    There is no relationship between two tables. You may create measures as below.

    Sum Qty over last 12 Months = 
var ym = MAX(Tab[YearMonth])
return
CALCULATE(
    SUM(Tab[Qty]),
    FILTER(
        ALLEXCEPT(Tab,Tab[Product]),
        Tab[YearMonth] in 
        TOPN(
            12,
            CALCULATETABLE(
                DISTINCT('Calendar'[YM]),
                FILTER(
                    ALL('Calendar'),
                    [YM]<=ym
                )
            ),
            [YM]
        )
    )
)

     

    STDEV Qty over last 12 Months = 
var ym = MAX(Tab[YearMonth])
return
CALCULATE(
    STDEV.P(Tab[Qty]),
    FILTER(
        ALLEXCEPT(Tab,Tab[Product]),
        Tab[YearMonth] in 
        TOPN(
            12,
            CALCULATETABLE(
                DISTINCT('Calendar'[YM]),
                FILTER(
                    ALL('Calendar'),
                    [YM]<=ym
                )
            ),
            [YM]
        )
    )
)

     

    Result:

     

    Best Regards

    Allan

     

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

3 Replies

  • stevedep's avatar
    stevedep
    Icon for Memorable Member rankMemorable Member
    5 years ago

    Calculate (stdev.p(values(product), calculate (sum(quantity)), datesinperiod(date,-12,month))

     

    Something like this..

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Ortignano 

     

    Based on your descirption, I created data to reproduce your scenario. The pbix file is attached in the end.

    Tab:

     

    Calendar(a calculated table):

    Calendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "YM",
    YEAR([Date])*100+MONTH([Date])
)

     

    There is no relationship between two tables. You may create measures as below.

    Sum Qty over last 12 Months = 
var ym = MAX(Tab[YearMonth])
return
CALCULATE(
    SUM(Tab[Qty]),
    FILTER(
        ALLEXCEPT(Tab,Tab[Product]),
        Tab[YearMonth] in 
        TOPN(
            12,
            CALCULATETABLE(
                DISTINCT('Calendar'[YM]),
                FILTER(
                    ALL('Calendar'),
                    [YM]<=ym
                )
            ),
            [YM]
        )
    )
)

     

    STDEV Qty over last 12 Months = 
var ym = MAX(Tab[YearMonth])
return
CALCULATE(
    STDEV.P(Tab[Qty]),
    FILTER(
        ALLEXCEPT(Tab,Tab[Product]),
        Tab[YearMonth] in 
        TOPN(
            12,
            CALCULATETABLE(
                DISTINCT('Calendar'[YM]),
                FILTER(
                    ALL('Calendar'),
                    [YM]<=ym
                )
            ),
            [YM]
        )
    )
)

     

    Result:

     

    Best Regards

    Allan

     

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