Power BI: calculating STDEVX.P for 6 months
I am trying to calculate the most recent six-month STDEVX.P (not including the current month, so in May 2017 I would like STDEVX.P for the periods November 2016 to April 2017) for sales by product to further calculate variations in sales orders.
Sales data consists of daily transactions, so it contains the date of the transaction: iContractsChargebacks[TransactionDate]
and the units sold:, iContractsChargebacks[ChargebackUnits]
but if there is no sale within a certain period, then there will be no data for that month.
So, for example, July 1, the sales for the last 6 months were as follows:
Jan 100
Feb 125
Apr 140
May 125
Jun 130
March is out because there were no sales. So, when I calculate STDEVX.P on the dataset, it calculates it for 5 periods when there were actually 6, only one of them turns out to be zero.
At the end of the day, I need to calculate STDEVX.P for the current six month period. If, when pulling the monthly sales numbers, it comes back with only 3 periods (months), then it should accept the remaining 3 periods with a zero value.
I was thinking about manually calculating the standard deviation instead of using the DAX STDEVX.P formula and found these 2 links as reference on how to do it, the first being the closest to my need:
https://community.powerbi.com/t5/Desktop/Problem-with-STDEV/td-p/19731
Calculating standard deviation from value and frequency columns in Power BI ...
I tried to do this but still don't get the correct calculation. My code:
STDEVX2 =
var Averageprice=[6M Sales]
var months=6
return
SQRT(
DIVIDE(SUMX(
FILTER(ALL(DimDate),
DimDate[Month ID]<=(MAX(DimDate[Month ID])-1) &&
DimDate[Month ID]>=(MAX(DimDate[Month ID])-6)
),
(iContractsChargebacks[SumOfOrderQuantity]-Averageprice)^2),
months
)
)
* Note: instead of using the date parameters in the code, I created a calculated column in the date table that gives each Month a unique ID, makes it easier for me.
source to share
Your question will definitely be easier to answer with more detailed explanations regarding your model. For example. as you defined [SumOfOrderQuantity] and [6M Sales], as the error can definitely affect the final result. Also, knowing which output you are seeing is the output you expect will be helpful (using the example data).
My guess, however, is that your DimDate table is a standard date table (with one row per day), but you want the standard deviation by month.
The FILTER statement in your formula correctly limits the date range to 6 full months, but it will still have one row per date. You can confirm this in Power BI by going to Data View, selecting New Table under Modeling in the Ribbon, and placing a FILTER statement in:
Table = FILTER(ALL(DimDate), DimDate[MonthID]<=(MAX(DimDate[MonthID])-1) && DimDate[MonthID]>=(MAX(DimDate[MonthID])-6))
Assuming you have more than one day of sales in a given month, calculating variance by day instead of month will be flawed.
What I suggest to try:
Table = FILTER(SUMMARIZE(ALL(DimDate),[MonthID]), DimDate[MonthID]<=(MAX(DimDate[MonthID])-1) && DimDate[MonthID]>=(MAX(DimDate[MonthID])-6))
The extra SUMMARIZE statement means you only get one row for each MonthID, not 1 row for each date. If your [6M Sales] is the monthly average for all 6 months, and [SumOfOrderQuantity] is the monthly total for each month, then you should set up the calculation, variance, division by 6 and square root.
If you need to continue troubleshooting, remember that you can place the table on your canvas using the MonthID, SumOfOrderQuantity, and [6M Sales] labels and compare the numbers you expect at each step of the calculation with the numbers you see.
Hope it helps.
source to share
I faced a similar issue while trying to calculate the CV (standard / average) for SKUS based on sales data. I could use the Pivot-Unpivot function in the Power Query editor to do away with the months of missing sales problem:
1) Export data with any calculated columns
2) Re-import the data so that the calculated columns are also available in the powerful query editor.
3) Rotate data by month
4) Replaced zero values with 0
5) Unvivoted data
6) Close and apply the request
7) Add the calculated column for the coefficient of variation using the formula
CV = CALCULATE(STDEV.P(Table1[Value]),ALLEXCEPT(Table1,Table1[Product]))/CALCULATE(AVERAGE(Table1[Value]),ALLEXCEPT(Table1,Table1[Product]))
Thus, zero sales for skipped months will also count for both the standard deviation and the mean.
source to share