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
adrianonannini
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,
1 REPLY 1
OwenAuger
Super User
Super User

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?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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