Standard Deviation same day of week

Anonymous — Sat, 11 Nov 2023 10:45:26 GMT

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,

Re: Standard Deviation same day of week

OwenAuger — Sat, 11 Nov 2023 12:51:06 GMT

Hi Anonymous

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?

topic Standard Deviation same day of week in DAX Commands and Tips

Standard Deviation same day of week

Re: Standard Deviation same day of week