Consolidating data with same dates; calculating standard deviation for process control chart

[Marcus2]

Hi, here is a sample data set for the type of problem that I have.

Date	No Observations
1/1/2024	3
1/1/2024	5
1/8/2024	2
1/8/2024	3
1/15/2024	4
1/15/2024	6

I would rather group by date, then calculate my standard deviation in DAX.

Date	No Observations
1/1/2024	8
1/8/2024	5
1/15/2024	10

Using Excel Std Dev = 2.52

How can I group dates and then find standard deviation in DAX?  I would like weekly observations on my Process Control Chart.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

If you are analyzing by week, I suggest having Calendar Dimension table something like below.

Please check the below picture and the attached pbix file.

STDEVX.S function (DAX) - DAX | Microsoft Learn

Observation total: = 
SUM( Data[No Observations] )

Standard deviation: = 
STDEVX.S( ALL('Calendar'[Start of Week]), [Observation total:])