topic Re: Standard Deviation - last 7 days in DAX Commands and Tips

Standard Deviation - last 7 days

Anonymous — Fri, 10 Nov 2023 15:41:19 GMT

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?

Re: Standard Deviation - last 7 days

OwenAuger — Fri, 10 Nov 2023 19:31:15 GMT

Hi Anonymous

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

Re: Standard Deviation - last 7 days

Anonymous — Sat, 11 Nov 2023 10:43:00 GMT

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!