Hello - I'm looking to add a calculated field that calculates the last (rolling) 12 weeks' worth of sales and takes the standard deviation of that group of numbers.
I feel like I should be able to do this with Calculate & using "DATESBETWEEN" function, but I cannot seem to get the DATESBETWEEN function to work properly. It works when I enter my own choice of dates numerically, but I want it to be applied for the last 12 weeks' worth of data rolling weekly - So if I look up 12/7/2016, it would show me the standard deviation of the data from 9/7/2016 through 12/7/2016. If I look at 4/9/2017, it would show me the standard deviation of the data from 1/9/2017 through 4/9/2017. Does this make sense? Is there a better formula than what I have below since this one results in an error?
Thank you!
St.Dev Sales 12RollWks:=CALCULATE(STDEV.P([POUNDS]), DATESBETWEEN(LPShredsSales[*StartDate],LPShredsSales[*StartDate]-84,LPShredsSales[*StartDate]))
Hi Stephanie,
In your formula the second and third arguments in the DATESBETWEEN function (StartDate, EndDate) are referencing a whole column, but they need to be a single date. You can use the LASTDATE function to return the last date in the dataset like so:
=CALCULATE(STDEV.P([POUNDS]), DATESBETWEEN(LPShredsSales[*StartDate],LASTDATE(LPShredsSales[*StartDate]-84),LASTDATE(LPShredsSales[*StartDate])))
Assuming LPShredsSales is your fact table and column *StartDate contains your transaction dates.
Mynda
This gets rid of the error, but the values all come back as 0 which is not accurate. Ideally, I'd like to have the dates as columns and the skus as rows with this calculation for each date (last 12 rolling weeks standard deviation which would change/roll each week), but when I pivot as such using the calculation measure above, they all come back as 0 for every sku and date.
Thoughts if I'm missing something or why this is the case?
I was able to get the same formula above working with "AVERAGE" instead of "STDEV.P" for the Grand Total and for last week's data, but not for subsequent weeks' rolling 12. Standard deviation is still coming back as 0 no matter how I pivot it though.
Hi,
It would be much easier for us to give help if you can provide a sample file.
Br,
Anders
Hi Stephanie,
As Anders said, there's nothing more we can do without a sample Excel file. Can you upload something we can look at that contains your formulas and some sample data in the same structure as your question?
Mynda
