cancel
Showing results for
Did you mean:
Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Community Support

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 =
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 3
Community Support

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 =
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.

Super User I

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

Something like this..

Proud to be a Super User!

Resolver III

Please provide (a sample of) you data

Announcements