last 12 months standard deviation on a table

[Ortignano]

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

[v-alq-msft · Accepted solution]

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.

[stevedep]

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

Something like this..

[JW_van_Holst]

Please provide (a sample of) you data