cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## STDEV and VAR for Date Period

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

2 REPLIES 2
Community Support

Hi

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

Helper I

Hi Jimmi Tao,

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

Thanks

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors