Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.