converting an excel calculation to DAX

[boyddt_mn]

I have a user with a specific request for help converting an Excel spreadsheet into a Power BI report. The data table that he is using is below.

NC Rate Doc = Documentation / Production

3mo Avg is three month rolling average of NC Rate Doc

The Alert is 3mo avg+2*sqrt(3mo avg*(1-3mo avg) / Production)

OR

$E4+2*SQRT($E4*(1-$E4)/$B4)

I was able to find enough information to get the rolling average ( listed below) but the Alert is alluding me

Any help is greatly appreciated.

	a	b	c	d	e	f	g
1	Month	Production	Documentation	NC Rate Doc	3mo Avg	Alert	Action
2	Mar-21	11738	55	0.5%
3	Apr-21	11389	52	0.5%
4	May-21	10720	35	0.3%	0.4%	0.5%	0.6%
5	Jun-21	12167	58	0.5%	0.4%	0.5%	0.6%
6	Jul-21	11876	62	0.5%	0.4%	0.6%	0.6%
	Aug-21	13589	72	0.5%	0.5%	0.6%	0.7%
	Sep-21	12872	90	0.7%	0.6%	0.7%	0.8%
	Oct-21	12579	51	0.4%	0.5%	0.7%	0.7%
	Nov-21	12523	59	0.5%	0.5%	0.7%	0.7%
	Dec-21	12631	56	0.4%	0.4%	0.6%	0.6%
	Jan-22	12316	63	0.5%	0.5%	0.6%	0.7%
	Feb-22	14158	60	0.4%	0.5%	0.6%	0.6%
	Mar-22	11103	42	0.4%	0.4%	0.6%	0.6%

NCs R03M =

VAR NumberofMonths = 3

VAR LastSelectedDate = MAX('Datekey'[Date])

VAR Period = DATESINPERIOD('DateKey'[Date], LastSelectedDate, -NumberofMonths, MONTH)

VAR Result =

CALCULATE(

AVERAGEX(

VALUES('DateKey'[YearMonthnumber]),

[NC Count]

),

Period

)

RETURN

Result

[boyddt_mn · Accepted solution]

I was able to finally sort things out. I was making things much more difficult than they needed to be. This is the measure that I put together.

Alert = 
VAR Result =
    [NCs R03M]+2*SQRT(DIVIDE([NCs R03M]*(1-[NCs R03M]),'Key Measures'[Monthly Orders]))

RETURN
    Result