Standard Deviation same day of week

[adrianonannini]

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,

[OwenAuger]

Hi @adrianonannini 

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?