Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Ortignano
Helper I
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
v-yuta-msft
Community Support
Community Support

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

Hi Jimmi Tao,

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

Thanks

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors