Incorrect STDV.P in powerBI compared to excel STDV.P

[kushsoni]

All,

I am new to powerBI and DAX and was trying to calculate 5 weeks moving average and standard deviation. earlier I was doing everything in excel. 

below are the observations for the last 5 weeks and calculations done in excel:

wdate	Applications submit	Average	Stdv.p
1/4/2020	24720255
1/11/2020	21756257
1/18/2020	18802997
1/25/2020	17686517
2/1/2020	17586526	20110510.5	2753690

in power BI, the average comes out to be correct but STDV.P output is not matching

2/1/2020 - 

stdv.p = 993908

I have used the below formula for both average and standard deviation:

5 Weeks Moving average = CALCULATE(SUM('SQL Weekly'[application]),DATESINPERIOD('SQL Weekly'[w_date],LASTDATE('SQL Weekly'[w_date]),-35,DAY))/

CALCULATE(DISTINCTCOUNT('SQL Weekly'[w_date]),DATESINPERIOD('SQL Weekly'[w_date],LASTDATE('SQL Weekly'[w_date]),-35,DAY))

5 Weeks Moving STD.P = CALCULATE(STDEV.P('SQL Weekly'[application]),DATESINPERIOD('SQL Weekly'[w_date],LASTDATE('SQL Weekly'[w_date]),-35,DAY))

I further checked if I get an accurate sum for the last 5 weeks and it seems coming correct.

Any help will be great.

Thanks,

Kush

[littlemojopuppy · Accepted solution]

Hi.  Your measures are overly complicated which is what's causing the error.  Try these...

Average Applications Submitted:=AVERAGE([Applications submit])

Standard Deviation Applications Submitted:=STDEV.P(Data[Applications submit])

Five Week Moving Average:=CALCULATE(
		[Average Applications Submitted],
		DATESINPERIOD(
			'Calendar'[Date],
			LASTDATE('Calendar'[Date]),
			-35,
			DAY
		)
	)

Five Week Moving Standard Deviation:=CALCULATE(
		[Standard Deviation Applications Submitted],
		DATESINPERIOD(
			'Calendar'[Date],
			LASTDATE('Calendar'[Date]),
			-35,
			DAY
		)
	)

Checking the calculations against Excel...

What's in yellow is cutting and pasting your data.  Orange is me verifying the calculations in Excel.  Green is using the average and standard deviation measures above.  Hope this helps!