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.

Frequent Visitor

## Standard Deviation same day of week

Hello,

I have the following formula that calculates the standard deviation of my last 7 days of sales:

7D StDev =
VAR CurrentDate = MAX('Date Dim'[Report Date])
RETURN
STDEVX.P(
FILTER(
ALL('Date Dim'),
'Date Dim'[Report Date] >= CurrentDate - 6 && 'Date Dim'[Report Date] <= CurrentDate
),
CALCULATE(SUM('--Sales'[sales_units]))
)

Now I want to do something similar, following the same logic: I need to get the standard deviation of the 7 occurences of the day of week. For example:
10 November 2023 (Friday) = I need to get units from that day + past 6 fridays.

My Date Dim table has a column called Day of Week where Sunday = 0, Monday = 1, etc.

Thank you,
Super User

Here are a couple of options:

1. A reasonably concise version, which applies a LASTDATE filter first, then applies 7 week range and Weekday filters within that:

``````7W StDev (current weekday) =
CALCULATE (
CALCULATE (
STDEVX.P ( 'Date Dim', CALCULATE ( SUM ( '--Sales'[sales_units] ) ) ),
DATESINPERIOD ( 'Date Dim'[Report Date], MAX ( 'Date Dim'[Report Date] ), -49, DAY ),
VALUES ( 'Date Dim'[Day of Week] ),
REMOVEFILTERS ( 'Date Dim' ) -- precaution
),
LASTDATE ( 'Date Dim'[Report Date] )
)``````

2. Similar structure to your existing measure

``````7W StDev (current weekday) =
VAR CurrentDateRow =
INDEX ( 1, 'Date Dim', ORDERBY ( 'Date Dim'[Report Date], DESC ) )
VAR CurrentDate =
SELECTCOLUMNS ( CurrentDateRow, 'Date Dim'[Report Date] )
VAR CurrentDayOfWeek =
SELECTCOLUMNS ( CurrentDateRow, 'Date Dim'[Day of Week] )
RETURN
STDEVX.P (
FILTER (
ALL ( 'Date Dim' ),
'Date Dim'[Report Date] >= CurrentDate - 48
&& 'Date Dim'[Report Date] <= CurrentDate
&& 'Date Dim'[Day of Week] = CurrentDayOfWeek
),
CALCULATE ( SUM ( '--Sales'[sales_units] ) )
)
``````

3. Another alternative with STDEVX.P in CALCULATE:

``````7W StDev (current weekday) =
VAR CurrentDateRow =
INDEX ( 1, 'Date Dim', ORDERBY ( 'Date Dim'[Report Date], DESC ) )
-- Alternative:
-- TOPN ( 1, 'Date Dim', 'Date Dim'[Report Date] )
VAR CurrentDate =
SELECTCOLUMNS ( CurrentDateRow, 'Date Dim'[Report Date] )
VAR CurrentDayOfWeek =
SELECTCOLUMNS ( CurrentDateRow, 'Date Dim'[Day of Week] )
RETURN
CALCULATE (
STDEVX.P ( 'Date Dim', CALCULATE ( SUM ( '--Sales'[sales_units] ) ) ),
DATESINPERIOD ( 'Date Dim'[Report Date], CurrentDate, -49, DAY ), -- 49 day period ending on CurrentDate
CurrentDayOfWeek -- Apply CurrentDayOfWeek filter
)``````

Do any of these work for you?

Owen Auger
Blog

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