Standard Deviation - last 7 days

[adrianonannini]

I have a Sales table that is on an Order/Product level. This is the essential information:

Receipt_Date	Order_id	SKU_Code	Region	Country	Channel	Units
01/01/2023	XYZ	123	Asia	Japan	Retail	3
01/01/2023	XYZ	878	Asia	Japan	Retail	1
01/01/2023	ABC	123	Europe	UK	Digital	2
02/01/2023	PQP	420	Europe	Germany	Retail	5

As you can see:

- Each row of my table represents a product present in one order. So if a transaction (XYZ, on my example above) had more than 1 product, there will be one row for each product.

- If there was more than one unit of a product being sold on the transaction, it will be populated on the Units column (like orders XYZ, ABC and PQP).

On PowerBI, I am trying to sum the units for the last 7 days (which I had no trouble doing), and I want to get a standard deviation of units sold in the past 7 days (current day + 6 days prior to current day).

With that information, I will use different visualisation tools - line charts, tables, etc.

My problem with using STDEV.P(Units) is that it will calculate the standard deviation on a row level. And I need to do that on a daily level + considering the last 7 days as the population.

I also need to allow my users to filter this information by region, channel, country, etc, so I cannot work with daily aggregations.

Any ideas?

[adrianonannini]

Whilst it didn't completely solved my challenge, your post did spark some inspiration that helped me getting there:

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]))

    )

It's working perfectly. Thank you!

[OwenAuger]

Hi @adrianonannini 

You can use STDEVX.P for this, iterating over dates.

I'm going to assume you have a Date table (marked as a Date table) created with relationship to Sales[Receipt_Date].

Also say you have a measure

Units Sum = 
SUM ( Sales[Units] )

Then create a measure like this:

Units Standard Deviation (7 days) = 
CALCULATE (
    STDEVX.P (
        VALUES ( 'Date'[Date] ),
        [Units Sum]
    ),
    DATESINPERIOD ( 'Date'[Date], TODAY (), -7, DAY )
)

A shortened version that would also work:

Units Standard Deviation (7 days) v2 = 
STDEVX.P (
    DATESINPERIOD ( 'Date'[Date], TODAY (), -7, DAY ),
    [Units Sum]
)

Does this method work for you?

Regards