STDEV and VAR for Date Period

[Ortignano]

Hi all,

I have a fact table with different sales order and a Calendar table with WeekId (the numbers of week passed from the first week in calendar).

I would calculate for each category a weekly average of last 52 weeks and a standard deviation to crate a table as:

Category   Average  STDEv

A               0.23        0.51

B

C

The problem is that I have some week without sales (the values above for category A is given by only 12 values in 52 weeks and teh value of 0,51 is calculated with Excel

I use following formula:

Average=CALCULATE (
[QtyTot];
FILTER (
ALL ( 'Calendar' );
'Calendar'[WeekID] <= MAX ( 'Calendar'[WeekID] )
&& 'Calendar'[WeekID]
>= MAX ( 'Calendar'[WeekID] ) -51
)
)
/52

and it's works

but I use

STD=

STDEVX.P (
FILTER (
ALL ( 'Calendar' );
'Calendar'[WeekID] <= MAX ( 'Calendar'[WeekID] )
&& 'Calendar'[WeekID]
>= MAX ( 'Calendar'[WeekID] ) - 51
);
[QtyTot]
)

But this give me 0.29 that is wrong 

I guess that I need to address the week without sales but I don't know how solve that.

Thanks for the help

[v-yuta-msft]

Hi Ortignano,

Based on your description, [QtyTot] is a measure you have created, right? Which columns are the measure based on? Could you please provide more details about it?

Regards,

Jimmy Tao

[Ortignano]

Hi Jimmi Tao,

yes it's a measure QtyTot = SUM('Quantity]) where Quantity is the sales order.

Thanks