Calculate 12 month rolling and standard deviation population

[Kaikin]

Hi expert,

i am newbie in Power Bi.

I had been ask to calculate the Sales Volatility, which formula  (standard deviation of the entire population) / (Average past 12 months amount)

Please advise. thanks

[v-kaiyue-msft · Accepted solution]

Hi @Kaikin ,

I would like to confirm the valuable input provided by the @Sergii24 .  However, I noticed that more details are needed to fully understand this.

1. create MEASURE and calculate the standard deviation for the current year.

PopulationStandardDeviation =
CALCULATE(STDEV.P('Table'[Total]),FILTER(ALL('Table'),'Table'[Product] = MAX('Table'[Product]) && YEAR('Table'[date]) = YEAR(MAX('Table' [date]) )))

2. Create MEASURE to calculate the average of the last 12 months.

AveragePast12Months =
CALCULATE(
AVERAGE('Table' [TOTAL]),
FILTER(ALL('Table'), 'Table'[date] >= EOMONTH('Table'[date],-13) + 1 && 'Table'[date] <= MAX('Table'[date]))
)

3. Get their quotient.

SalesVolatility =
DIVIDE(
[PopulationStandardDeviation],
[AveragePast12Months]
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sergii24]

Hi @Kaikin, and welcome to PowerBI world! 🙂
Take a look at this article - Cumulative total – DAX Patterns

If you just starting working with PowerBI I strongly suggest you to watch DAX 101 video series from SQLBI it will help you a lot to understand how calcualtions in PowerBI are made.

Good luck!