Forum Discussion

epicleo's avatar
epicleo
Frequent Visitor
9 years ago

Calculating STDEV for most Recent 12-Months

Hello Everyone, 

As part of a larger calculation, I am trying to calculate standard deviation of sales for the most recent 12 Months using a DimDate table.

 

The problem I am having is figuring out how to calculate STDEV for the most recent 12 Months when a particular item has less then 12 months of sales history.  So, what I am attempting to do is calculate STDEV for each item up to the most recent 12 Months or as many months available given not all products have 12 months of history.

 

Your help is GREATLY appreciated!!

 

Thank you.

Need Help

2 Replies

    • epicleo's avatar
      epicleo
      Frequent Visitor
      9 years ago

      v-sihou-msft  Thank you for the info!!  The link you provided helped out, but my senario is a bit different.  In the example, the individule had to create his sample set by calculating the number of instances our ocurrances by multiplying the qty by frequency.  For me, my sample set is the sum of sales in the month.  In addiiton I include a date filter.  Here is what I came up with, but its not working well.  Can you provide any help?  Take care!!

       

       

      ST_DEV = CALCULATE(
      Var x_sum = SUM(iContractsChargebacks[ChargebackUnits])
      Var mean = DIVIDE(x_sum,6)
      VAR dev_sq_sum = POWER(x_sum- mean,2)
      return
      SQRT(dev_sq_sum/6),
      FILTER(ALL(DimDate),
      DimDate[Month ID]<=MAX(DimDate[Month ID])-1 &&
      DimDate[Month ID]>=MAX(DimDate[Month ID])-6
      )
      )